Accumulative sum leaser or first greater than value

[oscarvictory]

Hi all, maybe you can help me out with this question.

Here is my table from SQL:

and what I need is to display only the rows where ACUM sum includes STOCK amount:

 Many TKS in advance.

[Kushal_Chawda]

@oscarvictory  Is the ACCUM and STOCK are measure in chart or fields in table?

[oscarvictory]

Hi Kushal, they are just fields in the table.

[oscarvictory]

In fact I did try:

if( Aggr(RangeSum(above(Sum( STOCK) , 0, RowNo(TOTAL))),PRODUCT, STOCK ) > ACUM, STOCK )

But it did not work.

[Kushal_Chawda]

@oscarvictory  I see that for all product , you have used RCV_DATE descending to compare  ACCUM with STOCK, but for last product first Date should be 01/11/2021 so shouldn't it be 12 instead of 4?

[oscarvictory]

You are right, you can dismiss the last line, it was added manually in order to enlarge the sample. But obvuiously date is wrong.

[Kushal_Chawda]

@oscarvictory  try below expression with Product and rcv_date as dimension. Below is the expression for ACCUM. If you have other measures in table, you need to  replace highlighted part with that measure expression so that when you uncheck "Include zero value" (Qlik sense) or "suppress zero value" (QlikView) it will show the relevant rows

=if(aggr(rangesum(above(total Sum(ACCUM),0,RowNo())), PRODUCT,(RCV_DATE,(NUMERIC,DESCENDING))) <= min(total <PRODUCT>aggr(if(rangesum(above(total Sum(ACCUM),0,RowNo())) > sum(STOCK),rangesum(above(total Sum(ACCUM),0,RowNo()))), PRODUCT,(RCV_DATE,(NUMERIC,DESCENDING)))),
Sum(ACCUM),0)

[oscarvictory]

I have replaced the red expression by Sum(STOCK) because is the correct one, but it seems that something is wrong.

In this example it should have taken the three first rows = ACCUM 11, but is only taking two:

[Kushal_Chawda]

@oscarvictory  As per your example, is it not 5+10=15 which is already more than stock so should be two lines? In your original post example as well for product 98654, (2+5+7 =14) which is more than stock 8. Am I missing something?

[oscarvictory]

Yes 😁, I will try to explain better.

ACCUM means:

So, if STOCK is 11 it must take the first three lines, if STOCK was 12 it should include the fourth line.