Multi Row Formula

Question

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.

SAMPLE DATA.xlsx

JoshuaM · Answer

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

* The first multi-row tool associates a value from either the Amount or Elim Amount field
* The running total will sum these records to determine if they are below or above the Total Elimination Total
* 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.

Multi Row Formula.yxzp

tim-regas · Answer

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.