Rolling Sum with Aggr

[Wkong]

Hi experts,

I am currently trying to calculate cumulative values for production of different products on the same table. I currently have:
=Aggr(
Sum({<Date = {"<= [Ship Date]"}>}[Quantity Kg]),
[Category]
)

However, it seems that my date filter is not working, and the expression just returns the total sum of quantities for each category. I have made sure that the Ship Date field is in the correct format. Does anyone know a potential fix or another way for me to be able to do this?

For reference this is an example of what I want to have.

[Vegar]

Try something  like this. =aggr(rangesum(above(Sum([Quantity Kg]), 0, rowno(total))), [Category], [Ship Date])

[Wkong]

Thank you for your speedy reply Vegar, however it does not appear to be working.

For my purposes, I would want the first few values to look like this.

8091.5
1360
19040
13155.5
1360
19040
32195.5
51235.5
70275.5
89315.5
108355.5
127395.5

[vinieme12]

Try below

=aggr(rangesum(above(Sum([Quantity Kg]), 0, rowno())),  [Ship Date] , [Category] )

OR

=aggr(nodistinct rangesum(above(Sum([Quantity Kg]), 0, rowno())),  [Ship Date] , [Category] )

[Wkong]

Thank you for your response Vinieme12, unfortunately this did not work as the results now aggregate by category and date, meaning that instead of giving me the cumulative quantity, I am left with the total quantity produced per date, per category. 

Please let me know if you have any other ideas 🙂

[Vegar]

Hi all 

I created a mockup example to check why the suggested solutions by me and @vinieme12  did not work. I think the issue lies in the sort order of the aggregation. 

In my mockup example this expression does work for calculating the rolling sum. 

=aggr(rangesum(above(Sum([Quantity kg]), 0, rowno(TOTAL))), ([Category],(TEXT, ASCENDING)), ([Ship date],(NUMERIC,ASCENDING)))

Below is the script used for generating data for my mockup example.

[Order data]:
load
   [Order number],
   date#([Ship date],'D/M/YYYY') as [Ship date],
   Category,
   [Quantity kg]
inline [
Order number,  Ship date, Category,  Quantity kg
1,             3/4/2023,  Brass,     100
2,             22/1/2023, Steel,     200
3,             1/4/2023,  Wood,      300
4,             9/1/2023,  Plexiglass,400
5,             21/4/2023, Steel,     200
6,             21/4/2023, Wood,      300
7,             9/3/2023,  Plexiglass,400
8,             27/3/2023, Glass,     500
9,             19/3/2023, Plexiglass,400
10,            25/1/2023, Rubber,    600
11,            28/4/2023, Brass,     100
12,            2/5/2023,  Brass,     100
13,            28/2/2023, Brass,     100
14,            31/3/2023, Glass,     500
15,            8/4/2023,  Glass,     500
16,            18/2/2023, Rubber,    600
17,            12/4/2023, Wood,      300
18,            13/4/2023, Wood,      300
19,            17/3/2023, Rubber,    600
20,            10/1/2023, Steel,     200
];

(Please mark a response as an acceptable solution if this solves your issue. It will help others with similar problems in the future)

[Vegar]

In reply to my previous response.

Read more about sorting in aggr() function on the Qlik help pages: https://help.qlik.com/en-US/sense/February2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.... 

[Wkong]

Amazing, this is pretty much what I was looking for. However, I am having an issue where in the event multiple orders of the same category went through on the same day, only one value would show as the total aggregated during that date. Is there any way for me to get a separate value for each row?

I thought about using the order number but there are events where an order contains more than one product of the same category.

[Vegar · Accepted solution]

Try this adjustment to your expression:

=aggr(NODISTINCT rangesum(above(Sum([Quantity kg]), 0, rowno(TOTAL))), ([Category],(TEXT, ASCENDING)), ([Ship date],(NUMERIC,ASCENDING)))