alteryx Community

Alteryx Designer Desktop Discussions

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

Multirow Formula Question

8 - Asteroid

I am trying to do a calculation where I take the number of records closed on a particular day and subtract it from the total remaining based on the amount of records closed the day prior. Rather than get the number total closed over time which is what I'm coming up with now I want to get the total that remains.  I cannot figure out how to write a multi-row formula to do that, as I need to get first total remaining number by subtracting the amounts in the first row and then get the following total remaining by subtracting the count on the current row from the total remaining on current row -1.  See below for what I am currently getting vs what is desired and a screenshot of the formula I currently have.  If there is a better way of getting the desired results than to use a multi-row formula I am also open to that.

 Completed Date Count Original Count Total Remaining Desired Result [Null] 5 118 -5 113 2023-03-15 1 118 -6 112 2023-07-17 1 118 -7 111 2023-08-04 1 118 -8 110 2023-09-11 2 118 -10 108 2023-09-18 4 118 -14 104
3 REPLIES 3
15 - Aurora

I used the Multi-Row tool and created a new field [My Result] with this formula:

IF IsNull([Completed Date]) THEN [Original Count] - [Count]
ELSE [Row-1:My Result] - [Count]
ENDIF

12 - Quasar

This should work for you

if isnull([Row-1:NewTotal]) then [Original Count] - [Count] else [Row-1:NewTotal] - [Count] endif

If the NewTotal is Null, its just original count - count to start it off. After that, you are just subtracting the next count from the NewTotal that was calculated on the previous row.

8 - Asteroid

That worked perfectly for me, thank you for the help.

Labels