topic How to use set analysis to calculate percentage between dimensions in a pivot table? in QlikView

How to use set analysis to calculate percentage between dimensions in a pivot table?

QlikToFindOut — Wed, 09 Aug 2017 19:28:52 GMT

Hello,

I'm currently using set analysis to calculate the overall percentage a row in a pivot table is part of for the overall amount. For example, I have a pivot table that resembles the following:

Dimension 1	Dimension 2	Dimension 3	Sales %
Beverage			70%
	Juice		30%
		Apple Juice	20%
		Lemonade	70%
		Orange Juice	20%
	Soda		60%
		Coke	2%
		Dr. Pepper	98%
	Alcohol		10%
		Blue Moon	100%
Food			30%

Something like the above.

I was thinking of using something like:

sum({<CurYTDFlag = {1}>} TOTAL) / sum(Total)

But then I'm at a loss on how to incorporate Dimension1, Dimension2, and Dimension3 up above because as its collapsible, I wouldn't know how to catch those conditions. Any help would be appreciated, thank you!

Re: How to use set analysis to calculate percentage between dimensions in a pivot table?

sunny_talwar — Wed, 09 Aug 2017 19:32:33 GMT

Can you share the raw data behind this and what is the expected output based on collapsed and non-collapsed dimension

Re: How to use set analysis to calculate percentage between dimensions in a pivot table?

QlikToFindOut — Wed, 09 Aug 2017 19:35:32 GMT

Sorry, I wasn't clear. The final output of what I want is the Sales % column. That is the expected output. I'm trying to write a set analysis expression that will do what I have shown in that last column.