alteryx Community

# Alteryx Designer Discussions

SOLVED

## Subtracting row-1 data to get next value

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

 A R B 40 30000 40 126 3000-40 if [A]>[R] then [R] else [A] endif 444 5555 6666 444 50000 if [A]>[R] then [R] else [A] endif 555 50000-444 666

Thank you so much in advance

15 REPLIES 15
21 - Polaris

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] ?

8 - Asteroid

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

 student id A R B 1 40 30000 40 1 126 3000-40 if [A]>[R] then [R] else [A] endif 1 444 1 5555 1 6666 2 444 50000 if [A]>[R] then [R] else [A] endif 2 555 50000-444 2 666

21 - Polaris

Hi @Ekta

Here is how you can do it.

Workflow:

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

2. Formula to calculate B

Hope this helps : )

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

21 - Polaris

Hi @Ekta

Wouldnt B be null before R is calculated?

21 - Polaris

Hi @Ekta

Can you show calculation tracing. I am bit confused 😅

11 - Bolide

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.

8 - Asteroid

 Student Id A R B 1 40 3000 40 1 126 3000-40=2960 if [A]>[R] then [R] else [A] endif -  126 1 444 2960-126=2834 444 1 5555 2834-444=2390 2390 1 6666 2390-2390=0 0 2 444 50000 444 2 555 50000-444=49556 if [A]>[R] then [R] else [A] endif 555 2 666 49556-555=49001 666

Thank you

21 - Polaris

Hi @Ekta

will this do

workflow:

Hope this helps : )

Labels