Multi-Row Formula with Null values

[tanyaz]

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

[L_T]

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

[apathetichell]

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.

[tanyaz]

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

[apathetichell · Accepted solution]

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.

[tanyaz]

Thank you apathetichell.  This works!!!