Running Total per month, quarter, year

Hi all,

I'm trying to build a workflow calculating Running Total per month, quarter or year.

(Refer to Running Total Workflow screenshot)

During the build of workflow, I encounter the below problems (I marked the corresponding alphanumeric characters onto my workflow for your ease of reference):

1/ The Running Total shuffles the order of outputs, even though I sorted the data set by order date before calculating the Running Total per month, quarter, and year.

2/ Is there a shorter solution to calculate the Running Total per month, quarter, and year while keeping only the lump sum rows?

3/ In relation to request 2 above, I'm trying to calculate the Running Total per month, quarter, and year (keeping only the lump sum rows) by:
- Using the Summarize Tool to extract the last date in each month.
- Using the Join Tool to combine the original data set with the last date in each month.

However, I encountered three issues while doing this:

a) Similar to the issue that occurs in the Running Total Tool, the output in the Join anchor of the Join Tool is shuffled, even though I sorted the data set by order date before joining.

b) Before joining, several results of the Running Total are incorrect

E.g.:

- Mar 2016 (it should be 213.74 instead of 329.93)

- Aug 2026 (it should be 4215.79 instead of 4623.35).

(Refer to Running Total Result Comparison screenshot)

c) The final result does not contain the Running Total for several months, e.g., Mar 2016, Aug 2016, Nov 2016.

(Refer to Running Total Result Comparison screenshot)

4/ Other question not in relation to my workflow

I'm new to Alteryx Community, so may I know how to attach a screenshot directly onto my post (rather than attach its screenshot like I'm doing)?

I hope to receive your discussions/ solutions to resolve the above.

Running Total.yxmd

Running Total WorkFlow.png

Running Total Result Comparison.png

Transformation

Accepted answers

CoG

There are several issues at play.

First, it appears that Running Total is not your goal. You seem to only want the Totals for by month (and possibly by quarter and year), respectively. Is this correct?

I will share how to handle that, but first, for your learning, 2 of your questions relate to an issue in your workflow:

Issue 3b - You are using the Summarize Tool to capture the information below. This does not do what you were hoping it would because you have negative Profits in your dataset, which means that the Max_RunTot_Profit may hit its maximum value on a date other than the [Max_Order Date]. This is because each aggregation step runs independently (Imagine if you asked for the "First" [Profit] value and the "Last" [Order Date]). This explains why your incorrect outputs will only ever be larger than what you expected.
Issue 3c - Missing Totals. For the same reason above, except now you are joining on both [Order Date] and [Profit Total], since the two values are from different records occasionally, in those instances, a match will never be found and those records will not be part of the "Joined"/'J' output. You'll notice that all of the "missing" items were also the incorrect values from Issue 3b.

You can get the values you're looking for far more simply by using a few Summarize Tools:

Finally, Welcome to Alteryx, where there are many ways to solve a problem, but don't try to over-complicate things (which can be hard when you are first starting out). When posting there should be a button like this:

that you can use to add in-line images instead of attaching them.

Hope this helps and Happy Solving!

Sample Workflow.yxzp

All comments

KGT

I can't run the workflow as I don't have the input data handy and resisting the urge to go find it.

The sorting issue is happening due to parallelisation. You can stop this by turning off the AMP Engine in Runtime Settings on the workflow, however it shouldn't matter in this process.

I would be aggregating these to the relevant granularity (so 3 streams) using summarize tools and then doing the running total, then join it back together in whichever fashion you like. I think this is the key. I can see that on ToolId 23, you group by Quarter/Year. I'm pretty sure that will only calculate the totals within those groupings, not the running total of those groupings.

pandal25

Hi KGT,

Thank you for your swift response.

Sorry for my bad, I attach below the input data for your further execution.

May I know the rationale why AMP Engine in Runtime Settings may cause the sorting issue?

Superstore Sales.xlsx

CoG

There are several issues at play.

First, it appears that Running Total is not your goal. You seem to only want the Totals for by month (and possibly by quarter and year), respectively. Is this correct?

I will share how to handle that, but first, for your learning, 2 of your questions relate to an issue in your workflow:

Issue 3b - You are using the Summarize Tool to capture the information below. This does not do what you were hoping it would because you have negative Profits in your dataset, which means that the Max_RunTot_Profit may hit its maximum value on a date other than the [Max_Order Date]. This is because each aggregation step runs independently (Imagine if you asked for the "First" [Profit] value and the "Last" [Order Date]). This explains why your incorrect outputs will only ever be larger than what you expected.
Issue 3c - Missing Totals. For the same reason above, except now you are joining on both [Order Date] and [Profit Total], since the two values are from different records occasionally, in those instances, a match will never be found and those records will not be part of the "Joined"/'J' output. You'll notice that all of the "missing" items were also the incorrect values from Issue 3b.

You can get the values you're looking for far more simply by using a few Summarize Tools:

that you can use to add in-line images instead of attaching them.

Hope this helps and Happy Solving!

Sample Workflow.yxzp

KGT

AMP stands for Alteryx Multi-threaded Processing. So when those tools execute, they'll split the data into several chunks so that it can be processed separately. Hence, why it comes out the other side in a different order. By turning AMP off, you are reverting to the single threaded engine that doesn't chunk like that.

pandal25

Thank CoG,

It does help me a lot. I genuinely appreciate the time and effort you put into your responses, especially your dedication to helping me learn through your questions.

pandal25

Thank KGT,

I'll note on it for my further working on Alteryx.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703