Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
YoussefBelloum
Champion
Champion

rangesum above stock

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/2020A30
30/09/2020B45
30/09/2020C60
30/09/2020D75

 

Would you be able to take a look please guys ?

Thank you !

Regards,

Youssef

Labels (4)
2 Replies
edwin
Specialist III
Specialist III

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
Specialist III
Specialist III

of course the user selects a year rt?

edwin_0-1607626137316.png