Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
Would you be able to share a sample or sample data... because the expression itself seem to be okay
I could send you the app. By email or uploading...(how)..
Check here
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).
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
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))
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.