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.

Subtract two specific rows and show the result in a new row

Paddi
8 - Asteroid

Hi all,

 

I'm currently facing this issue.

 

I need to do the calculation like this: [difference]=[sum1]-[sum2],  and [sum1] and [sum2] would always be the 9th and the 10th row. it would be simple with formula when the data is shown in column, how can data be subtracted between two rows?

 

The sample data is as shown below. Only focusing on highlighted ones would be enough. Thank you.

Paddi_0-1669629424100.png

 

6 REPLIES 6
ShankerV
17 - Castor

Hi @Paddi 

 

Please use the multi-row formula, but you need to do it repeatedly for many rows.

 

But however you can easily achieved is using Multi-Field formula.

 

 

Many thanks

Shanker V

DataNath
17 - Castor

Hey @Paddi, here's one way you could go about this:

 

DataNath_0-1669630837331.png

 

You mention that sum1 and sum2 are always in certain rows and we can therefore then isolate them with Select Records:

 

DataNath_1-1669630862707.png

 

From there, we can then Transpose and re-Cross Tab our data to bring the two sums onto a single row so that we can calculate the difference:

 

DataNath_2-1669630925067.pngDataNath_3-1669630932018.png

DataNath_4-1669630939745.png

 

From here, we then just Cross-Tab the data again to get back to our original headers, ready for them to slot in nicely when we Union this new calculated row to the bottom of the existing data set:

 

DataNath_5-1669630981636.pngDataNath_6-1669630991698.png

 

The final Select and Formula tools here are just making the F1 field larger in size so that we can then populate it with 'Difference' instead of leaving it null:

 

DataNath_8-1669631035711.pngDataNath_9-1669631042612.png

 

Hope this helps - please shout up if you need any further help!

binuacs
20 - Arcturus

@Paddi One way of doing this

 

binuacs_1-1669631197519.png

 

 

Felipe_Ribeir0
16 - Nebula

Hi @Paddi 

 

One way of doing this

 

Felipe_Ribeir0_0-1669631375521.png

 

Paddi
8 - Asteroid

Thank you all! @Felipe_Ribeir0 @binuacs  @DataNath  @ShankerV 

Really excellent and useful solutions!!! It's amazing to see that the issue can be solved in so many different ways. Thanks again.

ShankerV
17 - Castor

@Paddi 

 

Happy to support any time!!!

 

 

Many thanks

Shanker V

 

Labels