Help in Set Analysis Expression

[#1]

Hi all,

I created a table with some

dimensions and three expressions(Sales,Recurring,%mix).

The Recurring expression  values will be either 1 or 0.

My '%mix' expression is

  Sum

({$<YEAR = {$(=Max(YEAR)-1)}>} SALES)
/
Sum(TOTAL {$<YEAR = {$(=Max(YEAR)-1)} >} SALES)

In my '%mix' expression  second part i.e.Sum(TOTAL {$<YEAR = {$(=Max(YEAR)-1)} >} SALES) should act based on recurring value. It should only take 'total ' of those records  whose recurring value is '1'

PS:Please find attached file for the table and its expressions.

Please let me know how can i change my ' %mix' expression

Any help would be appreciated.

Thanks in advance.

Best Regards,

Susvith

[MK_QSL]

Try This...

IF(COLUMN(2) = 1, (Sum({$<YEAR = {$(=Max(YEAR)-1)}>} EX_SHIPPING_SALES_AMOUNT)/

Sum(TOTAL {$<YEAR = {$(=Max(YEAR)-1)}>} EX_SHIPPING_SALES_AMOUNT)),0)

[swuehl]

Try

if(Recurring,

Sum({$<YEAR = {$(=Max(YEAR)-1)}>} EX_SHIPPING_SALES_AMOUNT)

/

Sum(TOTAL {$<YEAR = {$(=Max(YEAR)-1)}>} EX_SHIPPING_SALES_AMOUNT)

)

as Expression. Your zero Recurring lines will be hidden unless you deactivate 'Suppress zero values' on presentation tab.

[er_mohit]

uncheck suppres Zero value in presentation tab of chart properties

hope it helps