Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
LuisMa
Contributor II
Contributor II

Pivot Table 3rd column with variance/comparison ?

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:

image.png

 

Separate pivot table:

image.png

 

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

image.png

 

As mentioned before, 1 dimension:image.png

7 expressions:image.png

 

 

Can this value be measured in the same pivot table?

Any help will be useful.

Thanks in advance!

Labels (3)
2 Replies
LuisMa
Contributor II
Contributor II
Author

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

Brett_Bleess
Support (Former)
Support (Former)

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

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.