I am trying to do a % diff to a baseline, but am unsure of how to do so due to the organization of my dataset.
As per this screenshot:
I want to compare shipped_ops with a % diff calculation based on the value of the agg_month field against shipped_ops where agg_month = basline, but haven’t been able to work out how to use the percentDiff function (or any alternatives) to get me where I want to go.
Any tips or guidance on this would be greatly appreciated!
Hi @Sebastian_Batali are you trying to compare and get % difference like
agg_month of Apr : 11,739.21 with agg_month of Baseline 11,122.57 ?
like (11,739.21 - 11,122.57) / 11,122.57
Is my understanding correct ?
Thank you for confirming. To do this You need to make a new calculated field, which hold shipped_ops only for agg_month = baseline. Try below steps.
Clac#1 for agg_baseline
ifelse(agg_month=‘Baseline’, shipped_ops,0)
Calc#2 % difference
(shipped_ops - agg_baseline) / agg_baseline
And using filter condition, exclude “Baseline” row form the table
Let us know if this works.
Thanks for the quick and detailed response @Ashok. I went ahead and implemented as you suggested, I understand the logic you’re proposing, and I am aligned that this makes sense and is a solid approach to achieve what I am hoping.
The view is implemented as per the screenshot below.
Unfortunately, as you can see, the difference field is not populating. Do you have any suggestions? I have filtered and defined the calculated fields exactly as you described.
Hi @Sebastian_Batali,
For what you want to achieve, you’ll have to use the table calculation function sumOver.
Still following @Ashok’s approach of using two calculated fields, you have to adjust the first as follows:
Calculated field #1 (agg_baseline):
sumOver(ifelse({agg_month}="Baseline", {shipped_ops},0),[day],PRE_FILTER)
A small explanation of the calculation from above: It is important to aggregate the baseline reference values before applying the filter. That’s what the calculation_level PRE_FILTER allows you to do. To get one reference value per day, you have to partition it by that dimension.
Calculated field #2 (difference) can remain as suggested earlier:
({shipped_ops} - {agg_baseline}) / {agg_baseline}
I hope my solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking his answer as a “Solution”.
