topic Re: Accumulative sum leaser or first greater than value in App Development

Accumulative sum leaser or first greater than value

oscarvictory — Fri, 19 May 2023 16:32:34 GMT

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.

Re: Accumulative sum leaser or first greater than value

Kushal_Chawda — Sat, 20 May 2023 23:08:27 GMT

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

Re: Accumulative sum leaser or first greater than value

oscarvictory — Mon, 22 May 2023 07:56:28 GMT

Hi Kushal, they are just fields in the table.

Re: Accumulative sum leaser or first greater than value

oscarvictory — Mon, 22 May 2023 08:11:21 GMT

In fact I did try:

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

But it did not work.

Re: Accumulative sum leaser or first greater than value

Kushal_Chawda — Mon, 22 May 2023 09:43:53 GMT

@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?

Re: Accumulative sum leaser or first greater than value

oscarvictory — Mon, 22 May 2023 09:57:24 GMT

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

Re: Accumulative sum leaser or first greater than value

Kushal_Chawda — Mon, 22 May 2023 10:47:27 GMT

@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)

Re: Accumulative sum leaser or first greater than value

oscarvictory — Mon, 22 May 2023 11:26:55 GMT

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:

Re: Accumulative sum leaser or first greater than value

Kushal_Chawda — Mon, 22 May 2023 11:47:06 GMT

@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?

Re: Accumulative sum leaser or first greater than value

oscarvictory — Mon, 22 May 2023 14:22:19 GMT

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.

Re: Accumulative sum leaser or first greater than value

Kushal_Chawda — Tue, 23 May 2023 09:14:28 GMT

@oscarvictory try below. Highlighted in Green you can replace it with measure you want to show. Suppose you want to show ACCUM , so replace it with below

sum(aggr(rangesum(above(total Sum(Qty),0,RowNo())), PRODUCT,(RCV_DATE,(NUMERIC,DESCENDING))))

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

Re: Accumulative sum leaser or first greater than value

oscarvictory — Mon, 22 May 2023 16:19:11 GMT

Well, finally I add an equal to the comparison symbol and it worked as expected!!

Thank you very much!!