Nested if expression.

[deborahs]

Hello, I've been trying to get an expression work since quite some time now, any help on this would be greatly appreciated!

I have dynamic dimensions and measures as well as a climber selection filter for currencies.

//Net

if(vMeasure=1 and if(num(GetSelectedCount(Selection)))>0 then

if(Selection='AUD',

(SUM (\[CGL.Amount (AUD)\])),

if(Selection='GBP',

(SUM( [CGL.Amount (GBP)])),

if(Selection='SGD',

(SUM([CGL.Amount (SGD)])),

if(Selection='EUR',

(SUM( [CGL.Amount (EUR)]))) ) )

),else if(vMeasure=1 and if(num(GetSelectedCount(Selection)))<0,(SUM(CGL.Amount))))

//Inflow

if(vMeasure=2 and if(num(GetSelectedCount(Selection))>0,

if(Selection='AUD',(SUM(if(\[CGL.Amount (AUD)\]>0 ,

[CGL.Amount (AUD)\]))),

if(Selection='GBP',

(SUM(if([CGL.Amount (GBP)]>0 , [CGL.Amount (GBP)]))),

if(Selection='SGD',

(SUM(if([CGL.Amount (SGD)]>0 , [CGL.Amount (SGD)]))),

if(Selection='EUR',

(SUM(if([CGL.Amount (EUR)]>0 , [CGL.Amount (EUR)]))))

))

), (SUM(if(CGL.Amount>0 , CGL.Amount)))

)

//Outflow

if(vMeasure=3 and if(num(GetSelectedCount(Selection))>0,

if(Selection='AUD',

(SUM(if(\[CGL.Amount (AUD)\]<0 , \[CGL.Amount (AUD)\]))),

if(Selection='GBP',

(SUM(if([CGL.Amount (GBP)]<0 , [CGL.Amount (GBP)]))),

if(Selection='SGD',

(SUM(if([CGL.Amount (SGD)]<0 , [CGL.Amount (SGD)]))),

if(Selection='EUR',

(SUM(if([CGL.Amount (EUR)]<0 , [CGL.Amount (EUR)]))))

)

), (SUM(if(CGL.Amount<0 , CGL.Amount)))

)

This expression fails to generate a bar chart.

I tried debugging and came to conclusion that there's a mistake in my if(vMeasure=1 and if(GetSelectedCount(Selection)>0. The initial selection of 'Selection' field is not there so num(getselectedcount(selection)>0 does not get fulfilled and the expression is dropped. I tried if(vMeasure=1 and if(num(GetSelectedCount(Selection))<0 and since initial selection is 0, num(getselectedcount(selection)<0 got fulfilled and generated the chart.

My question is is that since qlik doesnt have else if, would it be possible to get this to work?

Regards,

Deborah.

[rogerpegler]

I've reformatted the Net expression to be "correct" for expression syntax below ie not relying on if..then..else which can be used in load scripts.

Note :  there's no generic else clause to capture where vMeasure is not 1 or the count of the selection is zero.  If the intent is to have one expression that covers Inflow and Outflow as well then you can continue to expand the expression below  by changing (SUM(CGL.Amount))) into (SUM(CGL.Amount)) ,if(condition,then,optional_else) ) ie inserting more nested if statements inside the final closing bracket.

//Net
if(vMeasure=1
if(num(GetSelectedCount(Selection)))>0,
if(Selection='AUD',
(SUM (\[CGL.Amount (AUD)\])),
if(Selection='GBP',
(SUM( [CGL.Amount (GBP)])),
if(Selection='SGD',
(SUM([CGL.Amount (SGD)])),
if(Selection='EUR',
(SUM( [CGL.Amount (EUR)]))
)))),
if(num(GetSelectedCount(Selection)))<0,
(SUM(CGL.Amount)))