asked on February 3, 2025
I have a data set, in this data set I have a measure called OBS_VALUE, and a dimension called cl_pea_id_v1, I need to calculate a new value in flexmonster pivot tool like: Unemployed persons = sum OBS_VALUE if cl_pea_id_v1 = 2. how to implement this formula.
Please note that both fields (OBS_VALUE and cl_pea_id_v1) are numeric
I tired many formulas shapes without any logical output.
Your support is highly appreciated
⋅ February 3, 2025
Hello, Ahmad!
Thank you for reaching out to us.
Kindly note that Flexmonster is designed to work with aggregated data, so you'll need to add an aggregation function to each measure in the formula. We recommend using min/max aggregations for the described case, so the formula would look as follows:
if(max('cl_pea_id_v1')==2, sum('OBS_VALUE'),0)
We have prepared a JSFiddle example for illustration: https://jsfiddle.net/flexmonster/odjxmup8/.
Hope you will find our answer helpful.
Kind regards,
Solomiia
⋅ February 3, 2025
I sent you an email please try to check it. it includes the following:
Thanks for the SWIFT response.
Unfortunately, it didn't work. I don't of it is important to mention that cl_pea_id_v1 includes the values 1: for employed, 2: for unemployed, and 3: for inactive.
Can you give me a formula like:
Unemployment rate = sum (OBS_VALUE) where cl_pea_id_v1 =2 / (sum (OBS_VALUE) where cl_pea_id_v1 =2 + sum (OBS_VALUE) where cl_pea_id_v1 =1)*100
Thanks a lot for your support
⋅ February 4, 2025
Hello, Ahmad!
Thank you for providing us with additional details about the case.
As we mentioned earlier, Flexmonster tends to work with the aggregated values in the formulas. To make it use the value of each data row instead of the aggregated value in the formula, you need to enable the individual property. You can find a hands-on example by the link: https://www.flexmonster.com/doc/calculated-values/#individual-example.
Here is the JSFiddle showing how to calculate the unemployment rate using individual property and a couple of calculated measures: https://jsfiddle.net/flexmonster/gwou2dtp/.
However, please note that the individual property works only for JSON/CSV data sources. For other cases, we recommend preprocessing data before sending it to Flexmonster, e.g., to have two fields - one with all active customers and the other with all unemployed ones, and then just apply the formula on the cilent side. We have prepared a JSFiddle to illustrate the idea: https://jsfiddle.net/flexmonster/6pwv9c18/.
Hope you will find our answer helpful.
Kind regards,
Solomiia
