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

[#1]

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

[stephencredmond]

Hi Bob,

Is "ThisYear" a variable?

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

Regards,

Stephen

[#3]

HI Stephen,

Yes it is.

Thanks

Bob

[#4 · Accepted solution]

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

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