Calculate measures difference in a pivot table

[newqlik2017]

Original table (pivot chart) is my source.

Final table is the pivoted version of Original

Result is what's FINAL plus addition of 3 new columns (DIFF1, DIFF2, DIFF3). So I need to calculate
1) difference between 201606 n 201609 for ABC n DEF on column DIFF1
2) difference between 201609 n 201612 for ABC n DEF on column DIFF2

3) difference between 201612 n 201703 for ABC n DEF on column DIFF3.

Suggestions, please.

[newqlik2017]

Thank you. One more question:

For YR_MTH = 201703 (in this case; it will always & ONLY be the latest yr_month), the result should include an exchange rate. For remaining YR_MTH, it's the default value.  I might be missing something here ?

Pick(Dim,

if(Sum({<YR_MTH = {"$(=Min(YR_MTH, 4))"}>} COMPANY) = SUM(COMPANY), SUM(COMPANY) * 1.05,

Sum(COMPANY)),

Sum({<YR_MTH = {"$(=Min(YR_MTH, 2))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 1))"}>} COMPANY),

Sum({<YR_MTH = {"$(=Min(YR_MTH, 3))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 2))"}>} COMPANY),

Sum({<YR_MTH = {"$(=Min(YR_MTH, 4))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 3))"}>} COMPANY))

[sunny_talwar]

May be this

Pick(Dim,

RangeSum(Sum({<YR_MTH = {"$(=Min(YR_MTH, 4))"}>} COMPANY) * 1.05), SUM({<YR_MTH -= {"$(=Min(YR_MTH, 4))"}>}COMPANY)),

Sum({<YR_MTH = {"$(=Min(YR_MTH, 2))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 1))"}>} COMPANY),

Sum({<YR_MTH = {"$(=Min(YR_MTH, 3))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 2))"}>} COMPANY),

Sum({<YR_MTH = {"$(=Min(YR_MTH, 4))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 3))"}>} COMPANY))

[newqlik2017]

That didn't work. It actually placed 0 value on first 4 YR_MTH columns and placed a result on DIFF1 column.

[sunny_talwar]

There was a slight typo... but the most recent quarter numbers are multiplied by 1.05

Pick(Dim,

RangeSum(Sum({<YR_MTH = {"$(=Min(YR_MTH, 4))"}>} COMPANY) * 1.05, SUM({<YR_MTH -= {"$(=Min(YR_MTH, 4))"}>}COMPANY)),

Num((Sum({<YR_MTH = {"$(=Min(YR_MTH, 2))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 1))"}>} COMPANY))/100, '#.0%'),

Num((Sum({<YR_MTH = {"$(=Min(YR_MTH, 3))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 2))"}>} COMPANY))/100, '#.0%'),

Num((Sum({<YR_MTH = {"$(=Min(YR_MTH, 4))"}>} COMPANY) - Sum({<YR_MTH = {"$(=Min(YR_MTH, 3))"}>} COMPANY))/100, '#.0%'))

[newqlik2017]

Hi Sunny, it's working to an extent but what the RangeSum() expression is doing is doubling the number first (before multiplying the exchange rate). I wonder what's causing the number to double exactly.

[sunny_talwar]

I don't see any doubling taking place above, do you?

[newqlik2017]

You right, it doesn't double in your example. I applied the same logic in expression and it doubles the value for me. Here's the expression applied. I thing I did notice was, if I change the value on the RangeSum line from 4 to 3 or 2 or 1, it doubles the value for that column with the corresponding quarter. So, if it's 4, value doubles for 1Q 2017, if it's 3, value doubles for 4Q 2016, if it's 2 value doubles for 3Q 2016 and if it's 1 value doubles for 2Q 2016.

=Pick(Dim,

RangeSum(Sum({<RUN_ID = {"$(=Min(RUN_ID, 4))"}>} METR_AMT) * 1.05, SUM({<RUN_ID -= {"$(=Min(YR_MTH, 4))"}>}METR_AMT)),

Num(Sum({<RUN_ID = {"$(=Min(RUN_ID, 2))"}>} (METR_AMT)) /
Sum({<RUN_ID = {"$(=Min(RUN_ID, 1))"}>} (METR_AMT)) -1, '#.%')
,
Num(Sum({<RUN_ID = {"$(=Min(RUN_ID, 3))"}>} METR_AMT) /
Sum({<RUN_ID = {"$(=Min(RUN_ID, 2))"}>} METR_AMT) -1 , '#.%')
,
Num((Sum({<RUN_ID = {"$(=Min(RUN_ID, 4))"}>} METR_AMT) /
Sum({<RUN_ID = {"$(=Min(RUN_ID, 3))"}>} METR_AMT) -1 ) , '#.%'))

[sunny_talwar]

May be break down this part of the expression into two parts and see what is going on here

RangeSum(Sum({<RUN_ID = {"$(=Min(RUN_ID, 4))"}>} METR_AMT) * 1.05, SUM({<RUN_ID -= {"$(=Min(YR_MTH, 4))"}>}METR_AMT))

1) (Sum({<RUN_ID = {"$(=Min(RUN_ID, 4))"}>} METR_AMT) * 1.05

2) SUM({<RUN_ID -= {"$(=Min(YR_MTH, 4))"}>}METR_AMT)

Do you see any duplication?

Like, I mentioned, it would be difficult to help without seeing it.... I can guide you but that is about it