Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
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
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
of course the user selects a year rt?