Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculation in Chart (Pivot Table) ??

As easy as this may be for the pros out there, I am perplexed.

I have a pivot table, I am wanting an expression that give me the variance between the column labeled S14 and S7 (both of these columns are the same field "STTL_PUB_CD"  See below -

Thanks in advance for your help!

DawnPivot Table..JPG.jpg

1 Solution

Accepted Solutions
dirk_konings
Creator III
Creator III

You are using a pivot table with STTL_PUB_CD as a dimension value. You can't calculate this.

Try to add 2 expressions with SetAnalysis {<STTL_PUB_CD = {S7}>} or {S14} and add a 3th expression with the variance. Skip the STTL_PUB_CD as a dimension.

Dirk

View solution in original post

8 Replies
dirk_konings
Creator III
Creator III

You are using a pivot table with STTL_PUB_CD as a dimension value. You can't calculate this.

Try to add 2 expressions with SetAnalysis {<STTL_PUB_CD = {S7}>} or {S14} and add a 3th expression with the variance. Skip the STTL_PUB_CD as a dimension.

Dirk

MK_QSL
MVP
MVP

You can use...

=SUM(IF(STTL_PUB_CD= 'S7',Sales)) for S7

and the same for S14

Either SUM Or Whatever AGGR Function you want..

and Variance of the above two.

But as Mentioned by Dirk, Set Analysis will give you better performance

Not applicable
Author

Not sure what I am doing wrong in this set analysis you suggested, but this is what I get....Set Analysis Error.JPG.jpg

dirk_konings
Creator III
Creator III

sum({<STTL_PUB_CD = {S7}>} Value)

where value is the value you use in your other sum() expression

Dirk

Not applicable
Author

Excellent - Thanks so much - I got this to work....  now what is my calculation to get my variance?  (sorry, this part of Qlikview is the most confusing for me)

MK_QSL
MVP
MVP

You can do

Column(1) - Column(2) for difference and

1 - (Column(2)/Column(1)) for Percentage Difference

Make Sure for Percentage Difference under Number Table, you select Show in Percentage.

Not applicable
Author

Thanks so much!   I am finished!

MK_QSL
MVP
MVP

If this solved your problem, please close this thread by selecting correct answer.

Glad that my answer help you.