topic Re: Help with nested IFs in App Development

Help with nested IFs

jelindbe — Thu, 15 Mar 2018 12:16:53 GMT

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.

Re: Help with nested IFs

sunny_talwar — Thu, 15 Mar 2018 12:24:21 GMT

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

Re: Help with nested IFs

jelindbe — Thu, 15 Mar 2018 13:01:32 GMT

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

Re: Help with nested IFs

sunny_talwar — Thu, 15 Mar 2018 13:07:48 GMT

Check here

Uploading a Sample

Re: Help with nested IFs

jelindbe — Thu, 15 Mar 2018 14:35:32 GMT

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

sunny_talwar — Thu, 15 Mar 2018 14:37:59 GMT

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

sunny_talwar — Thu, 15 Mar 2018 21:29:05 GMT

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

Re: Help with nested IFs

jelindbe — Fri, 16 Mar 2018 07:37:27 GMT

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.