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