Calculating running total & overall revenue for same time last year?

[zara4580]

Hi all,

I have a table with daily revenue amounts for each combination of country + function + category + revenue stream. 

I've created two new columns: one for the running total for each month, and one for the overall total for each month (colored green in screenshot).

I need to find a way to create the additional four columns (colored yellow) in Alteryx. These columns are:

• The running total for the same date last year, and the same date 2 years ago 
• The overall monthly total for the same moth last year, and the same month 2 years ago

It's important that these new values are also for the same combination of each country + function + category + revenue stream, not just date, as there is far more data in my real dataset. Sample Excel attached.

I just can't figure out how to tell Alteryx to pull the correct values into my new fields. Any help is appreciated!

[Peachyco]

What I'd do is:

1. Create a new string field based on [Sale Date] that is formatted as "%m%d%Y" - let's call this string field as [Sale Date MDY]. This is important because your [Sale Date] is actually coming in different formats in Alteryx, even if they all look to have the same format in Excel.
2. Sort by the unique ID ([Country] + [Function] + [Category] + [Revenue Stream]) and [Sale Date MDY]. Because of the MDY formatting, the same dates for different years will fall right next to each other (e.g. 01152021 then 01152022 then 01152023).
3. Use the Multi-Row Formula Tool to target the records from 1 year prior (i.e. 1 row above) and from 2 years prior (i.e. 2 rows above), grouping by the unique ID. I'd add a check here to make sure that the row I'm targeting is actually 1 or 2 years prior, just in case there are missing/incorrect records.

[zara4580]

Thanks for your help, Peachyco! I totally get it in theory but I'm using the multi-row formula tool wrong somehow...it's just giving me a new column full of nulls (and un-does the sort). Any idea what I'm doing wrong?

[dwstada · Accepted solution]

don't include the sale date in your group by, otherwise you have only one row for this group, which will give you null values for row-1

the sorting is messed up because of the AMP engine i think, you need to sort again after each multirow formula tool (or disable the AMP engine for the workflow)

[zara4580]

Thanks @dwstada! That fixed the sorting. I'm still having trouble with the multi-row formula, I'm afraid - I want to say: "If the sales date of the previous row is exactly one year before the sales date of the active row, then return the running total from the previous row. Otherwise, return null". This is just resulting in all nulls, however.

Any ideas why this isn't working?

What I'm getting:

Desired result:

[dwstada]

Hey @zara4580 , sorry I didn't see this reply.

Incase this is still open I have attached a fix.

Additionally to the Sale Date MDY column, create another column with month and day, use this one in the group by of the multi-row formula tool. Then you should have groups for all month-day combinations of different years.