asked on May 10, 2022
Hello, Can you please provide details on how to calculate a variance between distinct count rows? We are trying to trend the data year over year, month over month, week over week, etc. and we want the ability to calculate the difference between distinct count rows. We tried to use difference by rows, but the calculations are not correct. It looks to be calculating the difference between the raw data and not the distinct counts.
⋅ May 11, 2022
Hello, Judy!
Thank you for writing to us.
Flexmonster has a feature for creating new measures with custom formulas - Calculated Values.
You can create calculated values in Flexmonster in the following ways:
You are welcome to see an example containing the delta calculation between two distinct count columns: https://jsfiddle.net/flexmonster/a79khw2L/.
Feel free to contact us if other questions arise.
Best Regards,
Maksym
⋅ May 18, 2022
Hello, Judy!
Our team would like to know whether you had time to try implementing variance between columns calculation via Calculated Values. Feel free to write us in case further assistance is required.
Best Regards,
Maksym
⋅ May 18, 2022
Thanks for the follow up, Maksym. The information was helpful, but didn't fully address our need. We often trend data by month and want to understand the difference by month vs. YA and then the total difference across the full time period. If unable to calculate the difference by month vs YA, are we able to add the distinct count variance to the total vs. each individual column? I've attached a few examples of how we look at the data.
⋅ May 20, 2022
Hi, Judy!
Thank you for providing the example.
The implementation of similar data calculations may differ depending on the data structure. Here are two approaches to months variance computing.
Calculate delta by data preprocessing
All the variances are calculated in a separate script. The script returns variances in a data format corresponding to the original one so that the month deltas can be appended to the original data and used in Flexmonster. You can place it on the client or server-side.
To ensure that monthly variances are shown in correct order, you should use the sortOrder property inside a slice column containing date labels.
We recommend this approach in case dates and values are stored in different fields.
Please see the example using this approach: https://jsfiddle.net/flexmonster/mwLyx5fq/.
Use calculated values
You can create month totals and year totals via calculated values. The year total would be the sum of corresponding months in this case.
Formula: 'sum("Sep 2020") + sum("Oct 2020") + ...other_months_if_present', where `sum` can be replaced by the required aggregation.
This approach is more straightforward but would only work with the data structure similar to the sample below.
You are welcome to check this JSFiddle: https://jsfiddle.net/flexmonster/ygceL2mp/.
Hope you will find this information helpful.
Best Regards,
Maksym
⋅ May 31, 2022
Hi, Judy!
We are wondering if you tried the suggested approaches for variance calculation. Looking forward to your feedback.
Best Regards,
Maksym
