topic Re: Calculate measures difference in a pivot table in QlikView

Calculate measures difference in a pivot table

newqlik2017 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculate measures difference in a pivot table

sunny_talwar — Mon, 02 Oct 2017 18:58:41 GMT

Do you need the exact format where the DIFF columns come after the Quarter columns? Also, will you ever look at only 4 quarter (essentially only 3 difference columns)?

Re: Calculate measures difference in a pivot table

sunny_talwar — Mon, 02 Oct 2017 19:04:01 GMT

One option could be to do like this

Where Diff is an expression like this

Sum(COMPANY)-Before(Sum(COMPANY))

But you won't get the formatting as your image

Re: Calculate measures difference in a pivot table

newqlik2017 — Mon, 02 Oct 2017 19:05:45 GMT

Yes, I need the exact format. Yes, I'll looking only 4 quarters and 3 difference columns.

I got the output - FINAL. Looking for output - RESULT (is possible, in one chart great; if not may be create a separate chart for DIFF1, DIFF2, DIFF3 and somewhere make it look like one chart)

Re: Calculate measures difference in a pivot table

newqlik2017 — Mon, 02 Oct 2017 19:10:14 GMT

Thank you Sunny. Unfortunately, this format won't work.

Re: Calculate measures difference in a pivot table

sunny_talwar — Mon, 02 Oct 2017 19:13:06 GMT

Here check this out

Re: Calculate measures difference in a pivot table

newqlik2017 — Mon, 02 Oct 2017 20:23:23 GMT

Sunny, this is very close to what I need. My TE column looks for different states - NY, CA, FL. So, my output RESULT will be for each state. I'll have 3 charts with RESULT chart - one for NY, one for CA and one for FL.

I do get the COMPANY amount as total value for DIFF1, DIFF2 and DIFF3 as it's adding the individual value for NY, CA and FL. I guess the expression needs to be modified to incorporate the state for DIFF1, DIFF2 and DIFF3 to show correct values at state level rather than summing them up for ABC or DEF. I'm getting error while modifying the expression. Can you provide insight please?

Re: Calculate measures difference in a pivot table

sunny_talwar — Tue, 03 Oct 2017 00:39:51 GMT

Not sure I understand... can you share a sample file here?

Re: Calculate measures difference in a pivot table

newqlik2017 — Tue, 03 Oct 2017 13:55:04 GMT

I slightly tweaked your query to meet my requirement and it's working. Thank you!

Also, the format for DIFF1, DIFF2, DIFF3 columns are in % but all other columns are in $ format. Since all these numbers are being derived from a single expression, it is possible to have them in different format?

Re: Calculate measures difference in a pivot table

sunny_talwar — Tue, 03 Oct 2017 13:59:32 GMT

Sure you can... Use Expression default formatting on the Number's tab... and then use Num to give different formatting for different part of the expressions

Pick(Dim,

Sum(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%'))

Re: Calculate measures difference in a pivot table

newqlik2017 — Tue, 03 Oct 2017 17:07:22 GMT

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))

Re: Calculate measures difference in a pivot table

sunny_talwar — Tue, 03 Oct 2017 17:41:02 GMT

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))

Re: Calculate measures difference in a pivot table

newqlik2017 — Tue, 03 Oct 2017 18:35:32 GMT

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

Re: Calculate measures difference in a pivot table

sunny_talwar — Wed, 04 Oct 2017 11:48:47 GMT

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%'))

Re: Calculate measures difference in a pivot table

newqlik2017 — Wed, 04 Oct 2017 14:26:17 GMT

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.

Re: Calculate measures difference in a pivot table

sunny_talwar — Wed, 04 Oct 2017 15:17:46 GMT

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

Re: Calculate measures difference in a pivot table

newqlik2017 — Mon, 09 Oct 2017 13:35:38 GMT

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 ) , '#.%'))

Re: Calculate measures difference in a pivot table

sunny_talwar — Mon, 09 Oct 2017 13:40:29 GMT

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