Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
7 - Meteor

Some finance processes in the tax world require you to monitor the balance of various accounts over time. The problem is, just like your personal bank account there isn’t always a change in balance every day. Meaning if you were to plot this data on a graph there would be gaps in the information for which there was no change. While the graph this data would generate is accurate, visually it would be misleading as it wouldn’t account for the days there were no change as demonstrated below. You’ll notice in the before graph changes in balance are very steep as it simply jumps to the next available date. Where in the after graph we can now see the periods where there was no change at all.

 

BEFORE_AFTER.PNG

 

The way to solve this in Alteryx comes in two steps, first we need to identify exactly what dates are missing data and create rows for them in our dataset. Then we need to run calculations on this data to determine what the balance should be on the days that had no change.

 

Step 1

 

To find the missing dates, I’m using an excel file I created which just contains a list of every date in the time period I’m interested in. This lets me then use a join tool with my balance data to find exactly which dates I’m missing information for. To reiterate, it’s not like this data is truly missing, it’s just that since there was no change to the balance reported on that day, the value should really be the balance of the date with the last reported change.

 

robertlenius_2-1589312624392.png

 

Step 2

 

To grab the balance from the last day reported I can use a multi row formula tool. This tool lets you create formulas that reference data vertically in your dataset, where a standard formula tool can only reference data horizontally. You’ll notice in the below example that I use two multi row formulas, the first one is filling in the empty balance fields, where the second one is actually creating a new column called “Balance Change” which calculates what the change in balance was.

 

robertlenius_3-1589312624398.png

 

The multi row formula tool can be a little bit tricky to use if you’re trying to type out these formulas by hand. My suggestion is to simply select the fields you’re interested in from the variables tab, that way you can ensure you’re working with the right field. For example, the formula I’m using is checking if data in the [Balance] field is null, if it is it will replace this value with [Row-1:Balance]. [Row-1:Balance] just refers to the value in the balance field one row up.

 

Overall, in addition to providing more visually accurate reporting this workflow creates a more complete dataset by filling in the gaps. Of course, it’s all dependent on your needs but you can adjust the process as needed. One thing that could be added is a field that calls out which dates had the balance calculated, and which are the original. That way if somewhere down the line someone is looking at this data, they can understand what was done.

Comments
12 - Quasar

@robertlenius 

 

Awesome post! 

thank you for sharing.

 

I also work in the accounting world and use-cases as you shared come up all the time! I don't think I can ever be without Alteryx on my work computer at this point. 

 

I also like to note in a separate field which data is exact from bank statements, etc. This way when I graph my data I can reference the exact points for the user of the dashboards I build out.

 

Feel free to DM to nerd out (tax accountants + Alteryx users = gods gift to the accounting industry) or connect on LinkedIn on my profile!

 

Sincerely,