topic Re: Calculation in Chart (Pivot Table) ?? in QlikView

Calculation in Chart (Pivot Table) ??

Tue, 11 Feb 2014 14:16:44 GMT

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!

Dawn

Re: Calculation in Chart (Pivot Table) ??

dirk_konings — Tue, 11 Feb 2014 14:20:51 GMT

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

Re: Calculation in Chart (Pivot Table) ??

MK_QSL — Tue, 11 Feb 2014 14:26:01 GMT

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

Re: Calculation in Chart (Pivot Table) ??

Tue, 11 Feb 2014 14:27:26 GMT

Not sure what I am doing wrong in this set analysis you suggested, but this is what I get....

Re: Calculation in Chart (Pivot Table) ??

dirk_konings — Tue, 11 Feb 2014 14:30:45 GMT

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

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

Dirk

Re: Calculation in Chart (Pivot Table) ??

Tue, 11 Feb 2014 14:43:27 GMT

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)

Re: Calculation in Chart (Pivot Table) ??

MK_QSL — Tue, 11 Feb 2014 14:58:24 GMT

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.

Re: Calculation in Chart (Pivot Table) ??

Tue, 11 Feb 2014 15:15:36 GMT

Thanks so much! I am finished!

Re: Calculation in Chart (Pivot Table) ??

MK_QSL — Tue, 11 Feb 2014 15:20:48 GMT

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

Glad that my answer help you.