community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to sum total with previous rows

Highlighted

Hello,

 

I'm creating a workbook which will show the applying a trade threshold and aggregation to a current trading process (currently, every request which comes in is immediately released as a trade) ... future state will be to aggregate based on a grouping of maturity month, releasing or holding week over week as cumulative trade request amounts reach the threshold.

 

The formula which I'm having trouble with should tell you what ACTION to take with this particular trade request ... Release, Hold, or Release as Spot.

 

Conditions are as follows:

RELEASE if the sum of all trades in this grouping created in this week or earlier which have NOT already been released is greater than the threshold amount

HOLD if the sum of all trades in this grouping created in this week or earlier which have NOT already been released is less than the threshold amount

RELEASE SPOT if we are told to held but it is the last trade requested for this month grouping

 

I'm stuck on creating the running total of held trades which have not been released, to add to the current trade amount and test against the threshold.

 

Current multi-row formula is:

if [Immediate Release]="Immediate Release" then "Immediate Release" elseif ([Sum_ABS Notional]>25000) then "Release" elseif ([Month Grouping]!=[Row-1:Month Grouping] && [Month Grouping]!=[Row+1:Month Grouping] && [Sum_ABS Notional]<25000) then "Release Spot" elseif ([Month Grouping]!=[Row-1:Month Grouping] && [Month Grouping]=[Row+1:Month Grouping] && [Sum_ABS Notional]<25000) then "Hold" elseif ([Month Grouping]=[Row-1:Month Grouping] && [Month Grouping]=[Row+1:Month Grouping] && [Sum_ABS Notional]+[INSERT RUNNING TOTAL OF HOLDS]<25000) then "Hold" elseif ([Month Grouping]=[Row-1:Month Grouping] && [Month Grouping]=[Row+1:Month Grouping] && [Sum_ABS Notional]+[INSERT RUNNING TOTAL OF HOLDS]>25000) then "Release" elseif ([Month Grouping]=[Row-1:Month Grouping] && [Month Grouping]!=[Row+1:Month Grouping] && [Sum_ABS Notional]+[INSERT RUNNING TOTAL OF HOLDS]<25000) then "Release Spot" elseif ([Month Grouping]=[Row-1:Month Grouping] && [Month Grouping]!=[Row+1:Month Grouping] && [Sum_ABS Notional]+[INSERT RUNNING TOTAL OF HOLDS]>25000) then "Release" else "Error"

ENDIF

 

Any ideas?

 

 

Alteryx
Alteryx

@julierasfeld 

 

Have you considered calculating these values in a separate stream (in this case leveraging Running total tool), appending these back as a new column and then using that to assign the label?

It's not only going to make it easier to achieve, it will also make it much easier to understand what's happening in the process. 

Labels