Running total of another running total

[RedBean]

I have orders that contain varying number of items. The majority of the items will have the same quantity, that is the number I am interested in for each order. So I group by order and do a running total with Nth most frequent with N = 1. Now there are various types of orders and I need a sum of the most frequent quantity by order type. But since it is not possible to have a running total of another running total field how can I go about doing this? Any help would be appreciated.

Example:
ORDER#     ITEM     QTY     TYPE
111111     AAAA      5       T1
111111     BBBB      5       T1
111111     CCCC      1       T1
111111     DDDD      5       T1
222222     GGGG      3       T1
222222     ZZZZ      8       T1
222222     QQQQ      8       T1
333333     TTTT     100      T2
333333     SSSS     100      T2

So what I need is for it to come up with:
T1 = 13
T2 = 100

 

[lbass]

Are you sure you want the sum at the type level of the Nth most frequent for each order? I can't see the logic of it--it would seem like you would instead want the Nth most frequent at the type level, so that in your example above the most frequent for type T1 would be 8.

Also, are you sure you need to use running totals instead of just inserting a summary (Nth Most Frequent) on {table.qty}? Do you have some conditions that don't allow you to do this?

Anyway, assuming you do want a sum, not just a higher order Nth Most Frequent, here is how you could do it. Please note though that this doesn't build in any of the conditions that might have caused you to use running totals, so you might have to adapt this.

First group on order and then on type. Create a reset formula {@resetSumNth} and place it in the Type group header:

whileprintingrecords;
numbervar sumNth := 0;

Then create a formula for the Group 2 footer (OrderNo) called {@SumNth}:

whileprintingrecords;
numbervar sumNth;
sumNth := sumNth + NthMostFrequent(1,{table.qty},{table.orderno});
sumNth;

-LB

 

[RedBean]

Thanks a lot for the help! It worked great. I know that my request seemed a bit strange, but the data I am working with is incomplete and this was the only way I knew that would get me the results I needed. Thanks again.