topic Re: Pivot Table and Totals in QlikView

Pivot Table and Totals

Wed, 26 Sep 2012 10:52:54 GMT

How can one use the totals from the pivot table on one of the raws to get a ratio of a different raw on the same table?. I can not use the static total that is there because it will change maybe even on a daily bases so I may need some code or something else.

Re: Pivot Table and Totals

whiteline — Wed, 26 Sep 2012 11:11:30 GMT

Hi.

Just use 'total' statement in the same expression.

For example:

=Sum(sales)/Sum(total sales)

The second sum discards all dimensions and return grand total. If you need a partial total you can use total<Dim1, Dim2> to keep some dimensions.

Re: Pivot Table and Totals

Wed, 26 Sep 2012 11:35:48 GMT

I have tried that one but doesnt return the correct ratios. The ratio is suppose to be the (expression/total) * 100

Is there no other way of doing this? Thanks for your response though....

Re: Pivot Table and Totals

whiteline — Wed, 26 Sep 2012 13:04:14 GMT

What is the problem with ratios, that they doesn't multiplied by 100 or something else ?

Coule you provide your expression ?

Re: Pivot Table and Totals

Wed, 26 Sep 2012 14:27:32 GMT

This is the expression for the above value

SUM(IF(vStatus = 'CXD',[CANCELLED],
REINSTATED))

And the expression for the Ratio calculation looks like this

SUM(IF(vStatus = 'CXD',[CANCELLED],REINSTATED))

SUM(TOTAL(IF(vStatus = 'CXD',[CANCELLED],
REINSTATED)))

The answers from the ratio expression are not the correct answers. Myltiplying by a 100 is not necessary as I have already ticked the percentage option on qlikview.

Re: Pivot Table and Totals

whiteline — Wed, 26 Sep 2012 14:46:56 GMT

I think you can't do it that way.

Try:

=Rangesum(Sum({<vStatus = {'CXD'}>} [CANCELLED]),Sum({<vStatus -= {'CXD'}>} [REINSTATED]))

/ Rangesum(Sum({<vStatus = {'CXD'}>} total [CANCELLED]),Sum({<vStatus -= {'CXD'}>} total [REINSTATED]))