Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Calculation Line

AlteryxLearner23
7 - Meteor

Hi,

 

I have the attached workflow with a sum calculation on the bottom.

 

I'm trying to enhance this workflow so there is a sum for every two lines.

 

At the moment we have this

 

Line 1 Expense         200
Line 1 Payments       -100
Line 2 Expense         300
Line 2 Payments       -50

Calculation                350

 

I want to change it to look like this

 

Line 1 Expense         200
Line 1 Payments       -100
Calculation                100
Line 2 Expense         300

Line 2 Payments        -50
Calculation                 250

 

Every set of data has one line for 'Expense' and one for 'Payments'.

 

Hope this makes sense.

8 REPLIES 8
ed_hayter
12 - Quasar

I went with a summarize tool to group by the ID to create the subtotal - then replicated the column structure so that we can union (stack) the sub-totals back into the original table. Part of that was creating a Forecast Type column for the Sub-totals all with the value =  "sub-totals" so that they can be filtered out when we want to.

ed_hayter_0-1685973097794.png

 

FrederikE
13 - Pulsar

Hey @AlteryxLearner23,

 

This Workflow should do that.

 

 

Edit: This is massivly overcomplicated, better take @ed_hayter's approach :D 

 

KimLamNg
8 - Asteroid

One way to do this with Crosstab and Transpose.

AlteryxLearner23
7 - Meteor

@FrederikE Thanks for the quick response!

 

Are you able to add the values and not subtract the values?

FrederikE
13 - Pulsar

Hey @AlteryxLearner23,

 

Yeah, that part is easy at my flow. You can just change the sign in the Multi-Row tool. 

FrederikE_0-1685973950813.png

 

The whole reason that I made it that complicated was that I thought you'd need to substract it :D

binuacs
20 - Arcturus

@AlteryxLearner23 Another approach with batch macro

binuacs_0-1685974247984.png

 

AlteryxLearner23
7 - Meteor

Hi @KimLamNg 

 

I changed some names in the "Forecast Type" column (attached) and I can't get your workflow to work.

 

Can you help?

 

Thanks!

KimLamNg
8 - Asteroid

@AlteryxLearner23 You just need to update the the formula tool that does the sum because the field names changed now. Attached is the updated workflow for your reference.

KimLamNg_0-1685991402226.png

 

Labels