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.
SOLVED

Subtracting row-1 data to get next value

Ekta
8 - Asteroid

Dear All, 

I am stuck at this formula, appreciate if you can help me please.

 

The data is like below, i have 3 columns and column A is prepopulate. I need to populate column R and B and they are interrelated.

we will have one row data populated like here data with  40 and 444 but following rows need to be populated with the logic

like for row with A= 126, R = 3000-40 and B with a logic and so on

 

ARB
403000040
1263000-40if [A]>[R] then [R] else [A] endif
444  
5555  
6666  
44450000if [A]>[R] then [R] else [A] endif
55550000-444 
666  

 

Thank you so much in advance

15 REPLIES 15
atcodedog05
22 - Nova
22 - Nova

Hi @Ekta 

 

Is there any grouping. Is there something like if [previous row]>[current row] then a new group starts and doesnt have calculation [current row] - [previous row] ?

Ekta
8 - Asteroid

@atcodedog05 Thank you so much for replying, yeah grouping is based on Student id. there are multiple other columns also.

 

 

student idARB
1403000040
11263000-40if [A]>[R] then [R] else [A] endif
1444  
15555  
16666  
244450000if [A]>[R] then [R] else [A] endif
255550000-444 
2666  

 

 

atcodedog05
22 - Nova
22 - Nova

Hi @Ekta 

 

Here is how you can do it.

 

Workflow:

atcodedog05_0-1628075383349.png

 

1. Using mult-row formula to calculate R. [row-1:R] - [row-1:A] 

2. Formula to calculate B

 

Hope this helps : )

Ekta
8 - Asteroid

@atcodedog05  oh looks like there is some misunderstanding:(

 

R = previous R -  B

and then B = if [A]>[R] then [R] else [A] endif

This is repeated will last row for student 1 and then same logic for student 2.

 

Thank you so much

 

 

atcodedog05
22 - Nova
22 - Nova

Hi @Ekta 

 

Wouldnt B be null before R is calculated?

atcodedog05
22 - Nova
22 - Nova

Hi @Ekta 

 

Can you show calculation tracing. I am bit confused 😅

Christina_H
14 - Magnetar

If I've understood correctly, this is giving the result you want although I'm not calculating it in the same way.

 

What you seem to be doing is subtracting all values of A for a student from R until there is not enough remaining, then reducing R to 0 by subtracting R instead of A.  I've done that by creating a running sum of A, filling in all values of R with the original value for the student, then subtracting the running sum from the previous row unless it exceeds R, in which case set R to 0 (this doesn't happen in your test data).  I then create B from those values, although depending on what you're doing that might not be necessary.

Ekta
8 - Asteroid

@atcodedog05  Thank you so much for your prompt reply.

please find the calculation tracing-

Student IdARB
140300040
11263000-40=2960if [A]>[R] then [R] else [A] endif -  126
14442960-126=2834444
155552834-444=23902390
166662390-2390=00
244450000 444
255550000-444=49556if [A]>[R] then [R] else [A] endif 555
266649556-555=49001666

 

Thank you

atcodedog05
22 - Nova
22 - Nova

Hi @Ekta 

 

will this do

 

workflow:

atcodedog05_0-1628078806214.png

 

Hope this helps : )

 

Labels