We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Multi Row Formula

bsolove123
6 - Meteoroid

I am trying to calculate the NEW ELIM AMOUNT column in the sample dataset I attached. For each GroupKey, I want the value of the AMOUNT column to eliminate in column H to the extent of the TOTAL ELIM ALLOWED column. I need to take into consideration amounts in the Elim Amount column that are already calculated. 

2 REPLIES 2
tim-regas
8 - Asteroid

Hi, it might be helpful if you uploaded a version with one group already calculated to get a better idea of what the calculations should be doing. Here is an example of a multi row formula that will calculate the cumulative elim for each group. We can expand on this for the other calculations once I have a better understanding of what should be calculated.Cumulitive Elim.png

JoshuaM
9 - Comet

Hi @bsolove123  the attached workflow uses two multi-row formula tools and a running total tool. 

  1. The first multi-row tool associates a value from either the Amount or Elim Amount field
  2. The running total will sum these records to determine if they are below or above the Total Elimination Total
  3. The last multi-row tool will adjust based on the criteria of being above the Total Elimination Total

The cool thing about the multi-row and running total tools is that they have an embedded select and groupby. So each calculation is performed at the GroupKey. Where the sorts serve as anchors to ensure the calculations are performed at the associated GroupOrder.

Snip.png

 

Labels
Top Solution Authors