Help with nested IFs

[jelindbe]

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.

[sunny_talwar]

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

[jelindbe]

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

[sunny_talwar]

Check here

Uploading a Sample

[jelindbe]

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

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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]

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.