topic Re: Can aggr() help with this? in QlikView

Can aggr() help with this?

Wed, 23 Oct 2013 16:43:12 GMT

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

Re: Can aggr() help with this?

tresB — Wed, 23 Oct 2013 16:53:31 GMT

Could you share a small sample app?

Re: Can aggr() help with this?

vikasmahajan — Thu, 24 Oct 2013 10:16:55 GMT

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

Re: Can aggr() help with this?

vikasmahajan — Thu, 24 Oct 2013 10:18:41 GMT

you can use dimension wise aggr to arrive total also

AGGR(formula ,dim)

Vikas

Re: Can aggr() help with this?

Fri, 25 Oct 2013 17:59:38 GMT

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

Re: Can aggr() help with this?

vikasmahajan — Sat, 26 Oct 2013 03:43:24 GMT

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

Thanks

Vikas