Hello-
I'm using a multi-row formula to calculate Quarter over Quarter Status. The formula appears to work well. However, I have entries where no information is provided (Null values) but the formula includes them in the QoQ calculation. If an entry doesn't contain any values I would like the QoQ to remain blank.
Multi-row Formula:
if [Quarter Date]>[Row+1:Quarter Date] and [As of Date]>[Row+1:As of Date]and
[Open Rate Grading] >[Row+1:Open Rate Grading] then "Down"
elseif [Quarter Date]>[Row+1:Quarter Date] and
[Open Rate Grading]<[Row+1:Open Rate Grading] then "Up"
elseif [Quarter Date]>[Row+1:Quarter Date] and
[Open Rate Grading]=[Row+1:Open Rate Grading] then "Same"
else ""
endif
Results: For Quarter Date 12/31/2020 the QoQ should be blank as the line below doesn't contain any information, but it currently calculates as if blank Quarter Date is valid for As of Date 11/30/2020
List of KPIs | Assessment | Quarter Date | As of Date | Assessment % | Open Rate Overall Status | Open Rate RAG | Metric | Open Rate Grading | Open Rate QoQ |
Open Rate | 0.12 | 3/31/2021 | 1/31/2021 | 12 | Poor | Amber | Open Rate | 2 | Down |
Open Rate | 0.3 | 12/31/2020 | 12/31/2020 | 30 | Very Poor | Red | Open Rate | 1 | Down |
Open Rate | 0 | 11/30/2020 | Open Rate | ||||||
Open Rate | 0 | 2/28/2021 | Open Rate |
Solved! Go to Solution.
Have you tried adding "and !isnull([Row+1:Quarter Date)]" to each of your conditions? I have run a dummy set of variables and seems to work
your easiest workaround may be either change all the null()s/empty to zero or add an !=null() clause to your if statement. Also - make sure you have your rows don't exist = 0 option set on multi-row formula.
Also - I'm sorry I should have realized earlier that your data is probably empty - not null()s and isnull() won't catch it.
Thanks L_T.
I have changed the expression but the results remain the same. Am I adding the !isnull in the wrong place?
if [Quarter Date]>[Row+1:Quarter Date] and [As of Date]>[Row+1:As of Date]and
[Open Rate Grading] >[Row+1:Open Rate Grading] and !isnull([Row+1:Quarter Date]) then "Down"
elseif [Quarter Date]>[Row+1:Quarter Date] and
[Open Rate Grading]<[Row+1:Open Rate Grading] and !isnull([Row+1:Quarter Date]) then "Up"
elseif [Quarter Date]>[Row+1:Quarter Date] and
[Open Rate Grading]=[Row+1:Open Rate Grading] and !isnull([Row+1:Quarter Date]) then "Same"
else ""
endif
Quick suggestion -
start with if !isempty([Row+1:Quarter Date]) then "" elseif (and past in your original formula)
That way you aren't adding isnull or isempty to every condition.
Thank you apathetichell. This works!!!