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?
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.
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?
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.
User | Count |
---|---|
52 | |
27 | |
25 | |
24 | |
21 |