Using Rangesum for accumulation in bar chart with filtering

[chaorenzhu]

Hi everyone, I have been trying to search answer for it for a long while, but couldn't really find a solution I expected and hence turning for your help. 

Basically I know we can create accumulation using Rangesum, and I'm able to create a bar chart, with dimension of Year and measure of =RangeSum(Above(Count(student_id), 0, RowNo())), to get cumulative count of students every year. It works fine, until I apply filter of Year in filter pane.

Let's say the data contains Year from 2010 to 2020. Now if I filter Year = 2015, instead of getting the accumulation from 2010 until 2015, the bar chart only shows the count in 2015. Is there a way to let the filtered bar show accumulation until selected value?

Another related question is although the data starts from 2010, I only want to show Year >=2015 in the axis of my bar chart, so in Dimension I set it as =if(Year>=2015,Year). Now the accumulation start from 2015 instead of 2010. How can I get accumulation from the start of the data, and at the same time only show Year >=2015 in my axis? 

Thank you very much!

[jbhappysocks · Accepted solution]

Hello

This calculation will give you the cumulative count until your highest selected year.

rangesum( above(count({<Year = {"<=$(=Max(Year))"}>}student_id),0,rowno()))  

This calculation will first calculate a balance going into 2015 and then add the cumulative count from 2015 and going forward until (highest) selected year. But use Year as dimension, not  =if(Year>=2015,Year)

count(total {<Year = {"<2015"}>} student_id)
+
rangesum( above(Count({<Year = {"<=$(=Max(Year))>2014"}>}student_id),0,rowno()))

[sunny_talwar · Accepted solution]

You can try this

=RangeSum(Above(Count({1} student_id), 0, RowNo())) * Avg(1)

[sunny_talwar]

Or this if you only need to exclude selection in Year field

=RangeSum(Above(Count({<Year>} student_id), 0, RowNo())) * Avg(1)

[chaorenzhu]

Thanks a lot! This is exactly what I expected!

I'm a newbie to Qlik Sense and still quite confused on set analysis. Would you mind explaining how Year = {"<=$(=Max(Year))"} does the trick? Thanks

[chaorenzhu]

Thanks Sunny! Could you explain what does Avg(1) mean? In my set analysis for Count I have any other modifier such as <region={'abc'}>, do I need to include the modifier in the avg(1) part?

[sunny_talwar]

Yes, any set analysis that you have in your count expression needs to be carried over to Avg(1).

Avg(1) is used because we are ignoring selection in Year field for the main expression... it will force the expression to show all years regardless of the selection made in year field. But Avg(1) will only show 1 for years selected. So, we are using Avg(1) to remove years which are not selected while still doing the accumulation for all years.

[chaorenzhu]

Got it. Thanks Sunny