Percent Diff to Subset of Field

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 ?

1 Like

Hey @Ashok, your understanding is correct.

Hi @Sebastian_Batali

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.

1 Like

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.

Agg_baseline is returning 0. Could it be because this is a pivot table as opposed to a basic table?

1 Like

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”.

1 Like