Cumulative sum per dimension thru RangeSum

[annick]

Hello,

I am displaying the cumulated sales over time as follows:

with following load script:

sales:
LOAD
* Inline [
Date, product, NbSales
30/08/2023, Dress, 1
29/08/2023, Pants, 1
28/08/2023, Dress, 1
27/08/2023, Pants, 1
];

and following formula: Rangesum(Above(Sum(NbSales),0, RowNo(TOTAL)))

Now I would like to do the same, adding a new dimension , per product:

However the formula Rangesum(Above(Sum(NbSales),0, RowNo(TOTAL))) would need to be adapted as it currently shows:

Could you please help?

Many thanks

Kind regards

Annick

[justISO]

Hi, try something like this:

Aggr(Rangesum(Above(Sum(NbSales),0, RowNo(TOTAL))), product, Date)

[annick]

Hello,

Thanks for your help, i am almost there but I am still missing something (maybe in the sorting?) as this is what I can now see:

I would like to have the sales increasing over time i.e. starting from 27th of August until 30th of August with the total sales.

Many thanks

[justISO · Accepted solution]

Yes, as you use above() function, your data from load script, ideally, should be ordered correctly. But, let's try another approach: in your line chart as Measure use only sum(NbSales), but right below it press 'Modifier' and choose Accumulation, set correct properties as Range=Full:

This should work as rangesum(), but without complex expressions writing.

[annick]

Thank you so much!

[annick]

Hello again,

So sorry to ask your help again, but I realized that this solution works fine for sums however it does not work for averages. I am trying to display the evolution of the average revenue  over time (i.e. Revenue / Nb of Sales) and it looks like I would need to use:

I would like to use the same chart for both the number of sales and the Average Revenue and unfortunately I am not able to switch dynamically from "Accumulation" to "Moving Average" option.

Therefore it looks like I need to use the initial RangeSum option.

Would you know how to update this formula:

Aggr(Rangesum(Above(Sum(NbSales),0, RowNo(TOTAL))), product, Id) 

without changing the load script?

Many thanks

Kind regards

[justISO]

Are you going to use separate measures for 'number of sales' and the 'Average Revenue'? One as measure and other as alternative measure? Because, I'm not sure how you going to put 2 measures into one otherwise. And in your rangesum() expression, try to use below() instead of after(). Maybe this can change something.