We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

How to not include nulls values in a running total

AbdulBalogun
7 - Meteor

I have a workflow that uses the running total tool to create a running total of sales in the year, the running total tool shows the average sales for each year. The issue is if a store has 0 sales for the month of January it's because that store was closed, so I don't want that month to be included in my running total and bring my average down. Is there a way to exclude null month/sales values so that they aren't included in my running total tool?

3 REPLIES 3
Yoshiro_Fujimori
15 - Aurora
15 - Aurora

Hi @AbdulBalogun ,

 

Do you mean like this?

 

Output

Yoshiro_Fujimori_1-1683845300808.png

 

If so, here is one solution.

 

Workflow

Yoshiro_Fujimori_0-1683845278740.png

 

Expression in Multi-Row Forumula tool

CountNonNull = 

IF !IsNull([Sales]) THEN [Row-1:CountNonNull] + 1
ELSE [Row-1:CountNonNull] ENDIF

 

Expression in Formula tool

RunningAverage = [RunTot_Sales] / [CountNonNull]

 

I hope this is of some help.

terry10
12 - Quasar

@AbdulBalogun   

 

You can use the Filter tool to omit the records with sales = 0, then calculate your average sales per year on the T records. After the calculation, you can Join back to the original data if you want.

 

Also, I think you are probably not using a Running Total, because that gives a sum, not an average. Here I show a summary tool that calculates average sales, grouping by year.

 

Does this answer your question?

 

Capture.PNG

SeanAdams
17 - Castor
17 - Castor

Hey @AbdulBalogun - I'd go with something like @terry10 mentioned above - using a filter to exclude null values (to exclude null you have to use ISNULL) and then use the running total after the filter.     As @terry10 says you can then link this back to your main record set.

 

 

Labels
Top Solution Authors