Accumulate Previous values using master calendar

[Adrian1]

Hi,

I have a table with warehouse movements like this:

PRODUCT	DATE_MOVEMENT	QUANTITY
A	15/05/2019	5
A	18/05/2019	-2
B	18/05/2019	3
C	20/05/2019	4
A	01/06/2019	-1
A	15/06/2019	5
B	30/06/2019	-2
A	06/07/2019	-1
A	06/07/2019	5
B	12/07/2019	3
C	13/07/2019	-2
A	04/08/2019	-1
A	15/08/2019	-1
B	16/08/2019	-2

And I need to accumulate the Quantity of each product in a timeline dimension, and make a chart table like this:

PRODUCT	DATE	ACCUM QTTY
A	31/05/2019	3
A	30/06/2019	7
A	31/07/2019	6
A	31/08/2019	4
B	31/05/2019	3
B	30/06/2019	1
…	…	…

Where for product A, the quantity at 30/06/2019 would be the sum of all previous movements.

Is it possible to do this without accumulate the values in the script?

I can obtain the accumulate for a single date using a variable with set analysis:

sum( {<DATE_MOVEMENT={">$(=date(MyDateVar))"} >}  QUANTITY)

But I’m not able to obtain this values with master calendar table.

Thanks in advance!

[sunny_talwar · Accepted solution]

Try something like this

Aggr(
    RangeSum(Above(Sum(QUANTITY), 0, RowNo()))
, PRODUCT, (DATE_MOVEMENT, (NUMERIC)))

[Adrian1]

It works! Thanks a lot!!