topic Re: Pivot table % in QlikView

Pivot table %

cbushey1 — Mon, 17 Oct 2016 20:52:16 GMT

Hi there,

I am looking for a way for my percentage expression to only display a percentage based on the total that is displaying. I understand that typically you can use the TOTAL prefix in the expression to get the data across the whole database but I need to only display percentages across the 12 months that are displaying.

My Numerator expression is as follows:

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

The two fields I am aggregating my CaseCounter over are the dimensions displayed in the pivot chart.

Suggestions?

Re: Pivot table %

sunny_talwar — Mon, 17 Oct 2016 20:54:46 GMT

So I am guessing that you are using Rolling12M_Flag to display 12 month period? If that is true, you should be able to do just this:

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))/Sum(TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

Re: Pivot table %

cbushey1 — Tue, 18 Oct 2016 12:58:57 GMT

Hi Sunny,

Thanks for the post, however I have tried that and it doesn't return the expected results. The problem is that the Total is ignoring my dimensions of Date_YM (i.e. October 2015...) and Cancellation reason and summing all values together. I know I have data outside of the last 12 months and unfortunately that is being included in the denominator.

I have also tried moving total to the inside Sum and many other variations with the Aggr to no avail.

Any other thoughts?

Re: Pivot table %

sunny_talwar — Tue, 18 Oct 2016 13:01:47 GMT

It might help to see a sample, but try these if you have not already done so

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))/Sum({<Rolling12M_Flag = {1}>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

Also, are you using calculated dimension or dimension limits in your chart?

Re: Pivot table %

cbushey1 — Tue, 18 Oct 2016 14:11:50 GMT

I think this might have done it for me.

Is it possible to have this calculation ignore nulls?

I found I also have some nulls in my CancellationReason field.

Re: Pivot table %

sunny_talwar — Tue, 18 Oct 2016 14:14:55 GMT

In the denominator or numerator or both?

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>} CaseCounter), Date_YM, CancellationReason))/Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>}CaseCounter), Date_YM, CancellationReason))

Re: Pivot table %

avinashelite — Tue, 18 Oct 2016 14:17:08 GMT

may be

Sum(Aggr(Sum({<Rolling12M_Flag = {1},CancellationReason ={'*'}-{''}>}CaseCounter),Date_YM,CancellationReason))/Sum({<Rolling12M_Flag = {1},CancellationReason ={'*'}-{''}>}CaseCounter>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

Re: Pivot table %

cbushey1 — Tue, 18 Oct 2016 14:56:08 GMT

Thanks! That did the trick for me.