Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
deborahs
Contributor III
Contributor III

Nested if expression.

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.

Labels (4)
1 Reply
rogerpegler
Creator II
Creator II

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)))