topic Re: Help in Expression in QlikView

Help in Set Analysis Expression

Mon, 25 Nov 2013 09:32:38 GMT

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

Re: Help in Expression

MK_QSL — Mon, 25 Nov 2013 09:38:02 GMT

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)

Re: Help in Expression

swuehl — Mon, 25 Nov 2013 09:38:06 GMT

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.

Re: Help in Expression

er_mohit — Mon, 25 Nov 2013 09:45:00 GMT

uncheck suppres Zero value in presentation tab of chart properties

hope it helps