Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: Nested if expression.

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

deborahs

Contributor III

2019-05-21
10:15 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

361 Views

1 Reply

rogerpegler

Creator II

2019-05-21
07:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

334 Views