topic Re: Pivot Table 3rd column with variance/comparison ? in QlikView

Pivot Table 3rd column with variance/comparison ?

LuisMa — Sat, 16 Nov 2024 01:53:37 GMT

Hello everyone,

I have a pivot table with 1 dimension (year) and 7 expressions.

Thanks to the year dimension, I can easily see the value of each expression divided by the year.

But I also need to build a % variance/comparison. It should be the variation from the prior year to the current year.

Please see the images below:

Original pivot table:

Separate pivot table:

Workaround (separate pivot table is behind the original pivot table):

As mentioned before, 1 dimension:

7 expressions:

Can this value be measured in the same pivot table?

Any help will be useful.

Thanks in advance!

Re: Pivot Table 3rd column with variance/comparison ?

LuisMa — Wed, 20 Nov 2019 20:21:15 GMT

Oh, I forgot to say something that might be important:

The separate pivot table that compares the years has no dimensions and 7 expressions.

All the expressions have set analysis to calculate the previous year vs current year.

Like this:

sum({< year={'$(=Max(year))'} >} _volume_tpm)
/sum({< year={'$(=Max(year)-1)'} >} _volume_tpm)

-1

Thanks in advance,

LMSR

Re: Pivot Table 3rd column with variance/comparison ?

Brett_Bleess — Wed, 04 Dec 2019 19:43:52 GMT

Luis, I may be missing something here, but I think the Column function may be what you are looking for on this one:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/InterRecordFunctions/column.htm

You would just specify the column(x)-column(y) etc... Not sure if I am missing something or not, hopefully this may be what you need. I was not sure if you had more years in the table or not as well, but I think you can just put in multiple expressions in that case using the correct column references etc...

Regards,
Brett