3 Replies Latest reply: Jul 3, 2009 6:27 PM by N G

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

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 :-(

Bob

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

Hi Bob,

Is "ThisYear" a variable?

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

Regards,

Stephen

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

HI Stephen,

Yes it is.

Thanks

Bob

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

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

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