Dynamic Totals

[tfinn]

Hi Everybody!

I use the following fields in a month-to-date report that is created daily:

Account Number

Count

Amount

Classification

The Classification field contains values like ‘Aged’, ‘Material’, ‘Variance Known’, ‘Exception’, and several others. 

I group these into three different categories and call the new field Item Classification:

Aged = Aged

Material = Material

Other = everything that is not Aged or not Material

I then need to summarize the Count and Amount at the Account Number level based on this new Item Classification field.  I’m doing all this with a combination of Summary, Cross Tab, Dynamic Rename and join tools.

I also need to create two additional fields called Total Item Count and Total Amount.  I handle this with a formula tool and couple basic formulas:

Total Item Count = ([Aged Item Count]+[Other Item Count]+ [Material Item Count])

Total Net Amount = ([Aged Net Amount]+[Other Net Amount]+[Material Net Amount])

This is where I have a problem.  Material Items are not common and sometimes don’t occur for the first several days of the month, so when my formula looks for Material Net Count and Material Net Amount, they don’t always exist and I get an error. 

The workflow goes through the original logic fine and just ignores the absence of Material rows, but it crashes when it gets to the hardcoded formula.  What is the best way to total these counts and amounts dynamically?

Thanks!

[DiganP]

@tfinn You can use the formula tool with a conditional statement and create a 0 for the dates that the Material Items don't exist. 

If isnull([ColumnA]) then 0 else [ColumnA] endif

I can give you the exact formula if you were to send the workflow with a sample dataset!

[JosephSerpis · Accepted solution]

Hi @tfinn the way I dealt with this where there is a hard coded formula is I use a dummy value to ensure that all columns are present and just filter out the dummy record. I've attached a workflow showcasing this.

[tfinn]

Thank you @JosephSerpis I was able to use a slightly modified version of your solution to resolve my issue.