Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)))