topic Re: %Mix in QlikView

%Mix

Fri, 21 Oct 2016 20:30:38 GMT

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%

Re: %Mix

Oleg_Troyansky — Fri, 21 Oct 2016 20:42:58 GMT

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.

Re: %Mix

Fri, 21 Oct 2016 21:30:35 GMT

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

Re: %Mix

johnw — Fri, 21 Oct 2016 23:20:23 GMT

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)

Re: %Mix

Sat, 22 Oct 2016 03:18:40 GMT

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)

Re: %Mix

Oleg_Troyansky — Sat, 22 Oct 2016 15:08:03 GMT

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

Re: %Mix

Sun, 23 Oct 2016 07:01:13 GMT

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

Re: %Mix

Oleg_Troyansky — Mon, 24 Oct 2016 14:52:27 GMT

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

Re: %Mix

Mon, 24 Oct 2016 16:22:03 GMT

Thanks, for your time