rangesum above stock

[YoussefBelloum]

Hi guys,

@sunny_talwar 

hope you're doing well !

I'm struggling on calculating a stock number using rangesum and above

here is a sample:

test:
LOAD *, Year(Date) as Year, 'Q'&Ceil(Month(Date)/3) as Quarter;
LOAD * Inline [
Date, code, number
30/09/2020, A, 15
30/09/2020, B, 20
30/09/2020, C, 25
30/09/2020, D, 30
30/06/2020, A, 10
30/06/2020, B, 15
30/06/2020, C, 20
30/06/2020, D, 25
30/03/2020, A, 5
30/03/2020, B, 10
30/03/2020, C, 15
30/03/2020, D, 20
];

I'm trying to calculate the sum of "number" column, it is a stock.

example: on Q3 quarter, number= 210

with a straight table, dimension= Year&' - '&Quarter, expression= =sum({<Quarter={$(=MaxString(Quarter))}>}aggr(rangesum(above(sum({<Year,Quarter>}number),0,RowNo(TOTAL))),(Year,NUMERIC),(Quarter,TEXT)))

is is working well.

the problem is that I'm struggling to make it work with the "code" column on the table...

When I select Q3 on the quarter I need to get this:

30/09/2020	A	30
30/09/2020	B	45
30/09/2020	C	60
30/09/2020	D	75

Would you be able to take a look please guys ?

Thank you !

Regards,

Youssef

[edwin]

here is a simpler solution.  create a Quarter field that is numeric bec you want to add it to your set analysis in the line of where Quarter <= a number.  text will also work but i would prefer to use numbers.  then create a variable that will store the selected quarter number

in your chart you can create a calculated dimension or expression for the date.  for your totals:
just sum where quarter<=selected quarter.  

im sure you can create a complicated expression and this is a simpler alternative

[edwin]

of course the user selects a year rt?