topic Set Analysis using TOTAL to get 'mix' column to work in QlikView

Set Analysis using TOTAL to get 'mix' column to work

Wed, 01 Jul 2009 18:02:49 GMT

Hi,

Just moved to 8.5 and now using the wonders of set analysis, but cannot get it to do something an old 'if' statement would. I am just trying to get the 'mix' column to work i.e. from previous column what percentage is sales of 'total' sales'

Month Sales Mix

Jan 100 10%

Feb 300 30%

Mar 600 60%

Used to use this...

[Sales]

/SUM(total(if (YEAR=ThisYear, SALES)))

where Sales was calculated in the column before

Now with sets...

[Sales)]

/ SUM(total ({$<YEAR={$(ThisYear)}>} SALES) )

but this doesn't work 😞

Any ideas gratefully received !

Bob

Set Analysis using TOTAL to get 'mix' column to work

stephencredmond — Wed, 01 Jul 2009 19:20:48 GMT

Hi Bob,

Is "ThisYear" a variable?

-> Sum(TOTAL { < Year = {$(=ThisYear) } > } Sales)

Regards,

Stephen

Set Analysis using TOTAL to get 'mix' column to work

Wed, 01 Jul 2009 19:30:59 GMT

HI Stephen,

Yes it is.

Thanks

Bob

Set Analysis using TOTAL to get 'mix' column to work

Fri, 03 Jul 2009 22:27:14 GMT

Found answer & for others... TOTAL must be at the end not the start... like the if statement

Sales / SUM( ({$<YEAR={$(ThisYear)}>} total SALES) )