How to not include nulls values in a running total

Question

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?

SeanAdams · Answer

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.

terry10 · Answer

@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?

Yoshiro_Fujimori · Answer

Hi @AbdulBalogun ,

Do you mean like this?

Output

If so, here is one solution.

Workflow

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.

RunTot_NonNull.yxmd