Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-Row Formula with Null values

tanyaz
6 - Meteoroid

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 KPIsAssessmentQuarter DateAs of DateAssessment %Open Rate Overall StatusOpen Rate RAGMetricOpen Rate GradingOpen Rate QoQ
Open Rate0.123/31/20211/31/202112PoorAmberOpen Rate2Down
Open Rate0.312/31/202012/31/202030Very PoorRedOpen Rate1Down
Open Rate0 11/30/2020   Open Rate  
Open Rate0 2/28/2021   Open Rate  
5 REPLIES 5
L_T
8 - Asteroid

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
18 - Pollux

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
6 - Meteoroid

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
18 - Pollux

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
6 - Meteoroid

Thank you apathetichell.  This works!!!

Labels