Rangesum with Aggr and If

[Daniel26]

Hello,

I have data that looks like this:

DAY	ITEM	QUANTITY	PRICE	TYPE	ITEMGROUP	CATEGORY
12.12.2023	A	1000	2,5	Stock	1	D
12.12.2023	B	500	5	Stock	2	E
12.12.2023	C	300	10	Stock	3	E
13.12.2023	A	200	2,5	Order X	1	D
13.12.2023	B	-400	5	Sales Y	2	E
13.12.2023	C	-50	10	Sales Z	3	E
14.12.2023	A	-500	2,5	Sales X	1	D
14.12.2023	B	-200	5	Sales Y	2	E
14.12.2023	C	150	10	Order Z	3	E
15.12.2023	A	-800	2,5	Sales X	1	D
15.12.2023	B	-400	5	Sales X	2	E
15.12.2023	C	100	10	Order Y	3	E
16.12.2023	A	-30	2,5	Sales X	1	D
16.12.2023	B	-50	5	Sales Y	2	E
16.12.2023	C	-10	10	Sales Z	3	E

I would now like to create a line chart and a combi chart, which display the cumulative sum of Quantity*Price.

The tricky part of the chart is the following logic:

When the quantity of an item gets <0 at a specific date, the cumulative sum of Quantity*Price should get 0 from this date onwards for all dates.

For example:

DAY	ITEM	QUANTITY	PRICE	cumulative Quantity*Price
12.12.2023	A	1000	2,5	2500
13.12.2023	A	200	2,5	3000
14.12.2023	A	-500	2,5	1750
15.12.2023	A	-800	2,5	0
16.12.2023	A	-30	2,5	0

DAY	ITEM	QUANTITY	PRICE	cumulative Quantity*Price
12.12.2023	B	500	5	2500
13.12.2023	B	-400	5	500
14.12.2023	B	-200	5	0
15.12.2023	B	-400	5	0
16.12.2023	B	-50	5	0

DAY	ITEM	QUANTITY	PRICE	cumulative Quantity*Price
12.12.2023	C	300	10	3000
13.12.2023	C	-50	10	2500
14.12.2023	C	150	10	4000
15.12.2023	C	100	10	5000
16.12.2023	C	-10	10	4900

So the whole cumulative sum should look like this (if I don't have an error while adding the numbers):

DAY	cumulative Quantity*Price
12.12.2023	8000
13.12.2023	6000
14.12.2023	5750
15.12.2023	5000
16.12.2023	4900

Furthermore it should be possible to filter for other masterdata of the item and the types of the Sales and Orders.

I have tried to use rangesum(), aggr() and if(), but I do not get the right results. I have tried something like

=if(rangesum(above(aggr(sum(QUANTITY*PRICE), ITEM, DAY),0,rowno()))<0,0,rangesum(above(aggr(sum(QUANTITY*PRICE), ITEM, DAY),0,rowno())))

, but this just works when I select one item.

I hope you have an idea or solution, how to solve this problem.

Thank you very much in advance.

Best regards,

Daniel

[Or]

Wrapping the whole calculation in NumMax(YourExpression,0) should prevent negative numbers. You'll still have to supply the correct running total expression, of course. You  may want to look at one of the many previous threads for that, e.g.

https://community.qlik.com/t5/New-to-Qlik-Sense/Running-total-per-dimension/td-p/1542119

[Daniel26]

Thank you very much, NumMax() is already helping me.

I was able to find the correct expresision for one item (it works, when I select one item):

nummax(Rangesum(Above(sum(QUANTITY*PRICE), 0, RowNo())),0)

When I try to generalize it for all items, I still have no idea how to solve it after searching through the board for possible solutions. When I try the following expression e.g. 

sum(aggr(nummax(Rangesum(Above(sum(QUANTITY*PRICE), 0, RowNo())),0),ITEM, DAY))

I receive 0 for every day.

The solution form the other thread

RangeSum(Above(TOTAL QUANTITY*PRICE,0,AGGR(RowNo(),ITEM,(DAY,(NUMERIC,ASCENDING)))))

is also not working for my problem.

Do you have an idea where my error in the structure of the expression lies?

Thank you!

Best regards,

Daniel

[vincent_ardiet_]

Could you try this one:
rangesum(above(sum({<QUANTITY={">0"}>}QUANTITY*PRICE),0,rowno(TOTAL)))

[Daniel26]

Hi Vincent,

thank you for your support! I have tried your solution, unfortunately it seems like the following days are all filtered out, because the just have negative values (only sales and no orders for those two items).

You can see in the Qlik_Board.png that when using sum(Quantity*Price) + Accumulation in the graph for those two items, the cumulative sum slowly decreases. 

Best regards,

Daniel

[vincent_ardiet_]

You mean that if at some point there is a quantity < 0, all past cumulation should be 0, right?
Based on the example you put initially, what is the expected result?

DAY	ITEM	sum(QUANTITY*PRICE)
12/12/2023	A	2000
12/12/2023	B	2500
12/12/2023	C	3000
13/12/2023	A	400
13/12/2023	B	-2000
13/12/2023	C	-500
14/12/2023	A	-1000
14/12/2023	B	-1000
14/12/2023	C	1500
15/12/2023	A	-1600
15/12/2023	B	-2000
15/12/2023	C	1000
16/12/2023	A	-60
16/12/2023	B	-250
16/12/2023	C	-100

[Daniel26]

Hi Vincent,

exactly, when the cumulative sum of the quantity of one item is <0 at a specific day, then the value should stay at 0 for this item from this day on.

When using the table you posted the solution should look like this, when selecting item A, B or C:

DAY	ITEM	sum(QUANTITY*PRICE)	CUMULATIVE
12.12.2023	A	2000	2000
13.12.2023	A	400	2400
14.12.2023	A	-1000	1400
15.12.2023	A	-1600	0
16.12.2023	A	-60	0
DAY	ITEM	sum(QUANTITY*PRICE)	CUMULATIVE
12.12.2023	B	2500	2500
13.12.2023	B	-2000	500
14.12.2023	B	-1000	0
15.12.2023	B	-2000	0
16.12.2023	B	-250	0
DAY	ITEM	sum(QUANTITY*PRICE)	CUMULATIVE
12.12.2023	C	3000	3000
13.12.2023	C	-500	2500
14.12.2023	C	1500	4000
15.12.2023	C	1000	5000
16.12.2023	C	-100	4900

And like this when selecting all 3 items (or select nothing, when those are all items in the data

DAY	CUMULATIVE	Calculation
12.12.2023	7500	2000+2500+3000
13.12.2023	5400	2400+500+2500
14.12.2023	5400	1400+0+4000
15.12.2023	5000	0+0+5000
16.12.2023	4900	0+0+4900

So this is the sum over all  3 cumulative sums of the items for every day (column calculation).

I hope this makes it more clear.

Thank you!

Best regards,

Daniel

[Or]

Pretty sure the NumMax() part needs to be outer-most. You seem to have placed it inside the aggr(), which means it will happen before the actual aggregation happens.

Generally speaking, I'd suggest removing that part entirely until you get the running total working correctly, and then figure out how to add it in so everything behaves as desired. It's easier to troubleshoot in parts.

[vincent_ardiet_]

As this accumulation doesn't seems to be dependent of the selections, I would suggest to do it at the script level instead. I've tried to play with RangeMax and Aggr() but this doesn't seems to work properly. And personally,  I'm always thinking that when we start to use a lot of Aggr this means that something is not done properly in the data model.

[Daniel26]

Thank you very much for your replies!

We were able to solve the problem with the following two master elements, whereas the second one is used in the graphs (DAY and MONTH is used as Dimension and alternative Dimension):

PER_ITEM:

If(Aggr(NoDistinct Min($(=GetObjectDimension(0))), ITEM) = $(=GetObjectDimension(0)),

RangeSum(Above(Aggr(

Sum(If(Aggr(NoDistinct Min($(=GetObjectDimension(0))), ITEM) = $(=GetObjectDimension(0)), QUANTITY*PRICE, 0))

,ITEM, $(=GetObjectDimension(0))),0, RowNo()))

, 

Aggr(

Sum(

    Aggr(RangeSum(Above(Aggr( Sum(QUANTITY*PRICE), ITEM, $(=GetObjectDimension(0))), 0, RowNo())), ITEM, ($(=GetObjectDimension(0)),NUMERIC, ASCENDING))

       )

, ITEM, $(=GetObjectDimension(0)))

)

PER_MONTH_OR_DAY:

Sum(Aggr(if(Sum(Aggr(PER_ITEM, ITEM, $(=GetObjectDimension(0))))<0,Null(),Aggr(PER_ITEM, ITEM, $(=GetObjectDimension(0)))),ITEM, $(=GetObjectDimension(0))))

Best regards,

Daniel