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.
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.
Hi @bsolove123 the attached workflow uses two multi-row formula tools and a running total tool.
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.
User | Count |
---|---|
59 | |
26 | |
24 | |
22 | |
21 |