Alteryx Designer Desktop Discussions

Arcane · ‎03-01-2022

Hello,

I am trying to perform a SUM operation for specific rows based on a column value. Below is the illustration.

Column1	Column2	Formula used	Alteryx Formula Used
ID1	10
ID2	20
ID3	30	ID1 + ID2	Row -1 + Row -2
ID4	40
ID5	50
ID6	60
ID7	150	ID5 + ID6 + ID7	Row-1+Row-2+Row-3
ID8	80
ID9	90
ID10	190	ID3 + ID7
ID11	110
ID12	120
ID13	530	ID1 + ID2 + ID5 + ID7 + ID10 + ID11	Row-12+Row11+Row-8+Row-6+Row-3+Row-2
ID14	140
ID15	150

Currently, I am using Multi-Row Formula tool. But, I don't find this an efficient way of doing a summation, as I have to count the rows and mention as Row-NUMBER.

Is there any way that, I can actually perform a SUM like, IF Column1=ID13 THEN SUM_(ID1 + ID2 + ID5 + ID7 + ID10 + ID11)

Looking for easy and better ways of performing this operation.

Thanks in advance :)

Qiu · ‎03-01-2022

@Arcane
I know it has to be Dynamic Replace tool. but still make it work...😅

atcodedog05 · ‎03-01-2022

Hi @Arcane

Does this work for you.

Hope this helps : )

Arcane · ‎03-01-2022

@atcodedog05, Thanks for your interest to provide help.

The objective is to get the SUM as shown in Column2. Your solution is giving a different output.

And the Formulas are static. There is no definite logic to perform SUM UP or SUM DOWN.

In the similiar fashion, I have around 150 rows. and 130th row is a sum of Row 50+65+69+83.

So, in that case, using ROW-50, Row-65 etc doesn't seem a right way ... Hence, looking for a better solution.

Arcane · ‎03-01-2022

@atcodedog05, Not really the solution I am looking for/ or trying to achieve.

In your example, the OUTPUT values doesn't match with Column2.

- Column2 is the final value I need to achieve.

- Currently, to acheive, I am using ROW-1, Row-2 etc method.

- But for the large dataset I have, somewhere in 150th record, it is the SUM of Recods (1+45+48+49+76). In this case, I have to count the number of rows backwards to 1,45,48,49,76 and write it as Row-149+Row-145 + Row-102 etc.