Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jelindbe
New Contributor II

Help with nested IFs

Hello,

My nested IF statemement looks like this:

if(type='STATISTIKK',Sum({$<År={$(=yearfrom)}>}[Folkemengde_31.12_stat]),

if(type='TILBUD',Sum({$<År={$(=baseyear-1)}>}Folkemengde_3112),

if(type='FRAMSKRIVNING',Sum({$<År={$(=baseyear-1)}>}Folkemengde_3112),

if(type='TRENDFLYTTING',Sum({$<År={$(=baseyear-1)}>}Folkemengde_3112),

'-'))))

The strange thing i that the first line does not return any results. The other if lines work like a charm.

But if I reduce the formula, like this:

if(type='STATISTIKK',Sum({$<År={$(=yearfrom)}>}[Folkemengde_31.12_stat]),

'-')


it works perfectly.


I cannot figure out why this happens. Any help would be welcome.

1 Solution

Accepted Solutions

Re: Help with nested IFs

Not sure why, but adding double quotes around your dollar sign expansion seems to have worked

if(type = 'STATISTIKK', Sum({$<År={"$(=yearfrom)"}>}[Folkemengde_31.12_stat]),

if(type = 'TILBUD', Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112),

if(type = 'FRAMSKRIVNING', Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112),

if(type = 'TRENDFLYTTING', Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112), '-'))))

Another way to write this if statement is to use Pick(Match(...))

Pick(Match(type, 'STATISTIKK', 'TILBUD', 'FRAMSKRIVNING', 'TRENDFLYTTING')+1,

'-',

Sum({$<År={"$(=yearfrom)"}>}[Folkemengde_31.12_stat]),

Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112),

Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112),

Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112))

7 Replies

Re: Help with nested IFs

Would you be able to share a sample or sample data... because the expression itself seem to be okay

jelindbe
New Contributor II

Re: Help with nested IFs

I could send you the app. By email or uploading...(how)..

Re: Help with nested IFs

Check here

Uploading a Sample

jelindbe
New Contributor II

Re: Help with nested IFs

The attachment was too big. I give you access to the app via dropbox instead. Here you are - Dropbox - KOMPAS-PROSJEKTER2.qvf

Move to the Sheet "Folkemengde-endring". You will find a bar chart and kpi's. They will be populated if you choose from "Prosjekt" in the filter pane. "Moss og Rygge" is a "type=TILBUD" project while "Statistikk Moss og Rygge" is a "type=STATISTIKK". You will see that the first one works fine (showing green and red values in the chart) but the second one does not show correct values (showing only red, no green values).

Under "Measures" you will find "Folkemengde i basisår" which is used in this sheet (for the green values).

Re: Help with nested IFs

Unfortunately, I won't be able to download this... may be someone with access to Dropbox can check or I will check this once I am home

Re: Help with nested IFs

Not sure why, but adding double quotes around your dollar sign expansion seems to have worked

if(type = 'STATISTIKK', Sum({$<År={"$(=yearfrom)"}>}[Folkemengde_31.12_stat]),

if(type = 'TILBUD', Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112),

if(type = 'FRAMSKRIVNING', Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112),

if(type = 'TRENDFLYTTING', Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112), '-'))))

Another way to write this if statement is to use Pick(Match(...))

Pick(Match(type, 'STATISTIKK', 'TILBUD', 'FRAMSKRIVNING', 'TRENDFLYTTING')+1,

'-',

Sum({$<År={"$(=yearfrom)"}>}[Folkemengde_31.12_stat]),

Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112),

Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112),

Sum({$<År={"$(=baseyear-1)"}>}Folkemengde_3112))

jelindbe
New Contributor II

Re: Help with nested IFs

Thank you very much! You have no idea how I have struggled with this - back and forth, up and down, in and out.

Both your statements worked out of the box. Your alternative statement seems much more elegant and easier to maintain. I must study Pick and Match.

Community Browser