%Mix

[#1]

Hello, i have a pivot table where i need to present %mix, but i have a dimensión Region , some like this, my problem is when i use the expression Sum(TOTAL Aggr(sum({<$(vSetYearAct),$(TipRegion)>}#Sales),CanalName)) for get sum of sales for region show sales for noth and south, not separate

Region	Nort		South
Canal Name	Sales	%Mix	Sales	%Mix
Canal 1	5472	33%	8712	52%
Canal 2	8769	52%	6478	39%
Canal 3	2547	15%	2145	13%
Total	16788	100%	17335	103%

[Oleg_Troyansky]

Hi Elina,

I don't think you really need the AGGR function for this calculation. I'd use the following:

SUM({<condition>} Sales)/sum({<condition>} Total Sales)

cheers,

Oleg Troyansky

Learn AGGR, Set Analysis, and many other advanced techniques from my book QlikView Your Business.

[#3]

I've already tried that option, but I don't have result. It could be because I have canal and region as dimension??

[johnw]

It looks like you want the %Mix within the Region? Just need to add <Region> after the total in Oleg's expression, I think.

sum({<$(vSetYearAct),$(TipRegion)>} #Sales)
/sum({<$(vSetYearAct),$(TipRegion)>} total <Region> #Sales)

[#5]

sorry, i don´t explain correctly, i have two dimensions. canal and regional, and i have two variables vSetYearAct and TipRegion, the last save if region is north or south, i tried your expression, but do not work.

(sum({<$(vSetYearAct),$(TipRegion)>}TOTAL <Regional>} #Sales)

[Oleg_Troyansky]

Hi Elina,

it would be a lot easier if you could create a small example and share it. We could then review it and help you find the solution.

cheers,

Oleg Troyansky

[#7]

thannks Oleg, attach file with examples, thanks for your help

[Oleg_Troyansky · Accepted solution]

!

Hi Elina,

you simply had an extra bracket } after the TOTAL qualifier. Attached is the corrected formula.

cheers,

Oleg Troyansky

Learn advanced QlikView and Qlik Sense techniques in my book QlikView Your Business

[#9]

Thanks, for your time