Can aggr() help with this?

[#1]

Hello - I am having trouble coming up with a solution for this need.  If you look at the chart below, you will see that "Item 1" has an "Ordered QTY" of 16.  We actually have 200 ("Item QTY") of that item in inventory.  However, showing all 200 in the pivot is not necessary, I only need to show enough quantity to fill the order.

I'm looking for a way to filter rows once the running total of the column "Item QTY" exceeds or meets "Ordered QTY".  For the "Item 1" example, I need to show rows 3, 4, and 5.  Rows 6,7,8 are not needed since the we have already displayed enough quantity to fill the order. 

Any suggestions?  I'm guessing it's easier than I'm making it.  I'm still trying to get the hang of QlikView.

Thanks in advance,

Tim

[tresesco]

Could you share a small sample app?

[vikasmahajan]

I think you can use rangesum with above command to achieve this requirement for every stage you want to calculate balance.

PFA small demo for the same ITEM QTY you can subtract.

Vikas

[vikasmahajan]

you can use dimension wise aggr to arrive total also

AGGR(formula ,dim)

Vikas

[#5]

Hello Vikas - Thanks for the help.  Your rangesum formula almost what I need.  The only part I'm missing is I need to it start over at each dimension.  In the example you sent me, I would need the function to start over at each break in the "Item Name" dimension.  Is there a way to modify the expression to do that? 

Also, thanks for pointing me to the rangesum function.  I don't have a good feel for how it works yet, but I can tell it will be very useful once I get it figured out. 

Thanks,
Tim

[vikasmahajan]

Please create small demo by loading data INLINE load and attach so that we can help you as per your requirement.

Thanks

Vikas