Bar Chart - accumulation

[wanyunyang]

My data has fields Year (2016,2017,2018) and ID. I want to create a bar chart with dimension: Year, measure: COUNT(ID). The thing is, I want the accumulated result. For example, if I select Year=2016, it will show me count of ID in 2016; if I select Year=2017, it will show me count of ID in 2016 & 2017, and so on.

How should I create the function?

Thanks for help! 

[sunny_talwar]

May be try this expression:

Count({<Year = {"$(='<=' & Max(Year))"}>}ID)

[wanyunyang]

Hi Sunny,

Thanks for helping! I gave it a go, but it didn't work. By using Count({<Year = {"$(='<=' & Max(Year))"}>}ID), if I choose 2017, the bar chart will show me two bars: 2016 & 2017, separately.

[sunny_talwar · Accepted solution]

Oh you want a cumulative sum in a single bar... try this

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

[wanyunyang]

It works! Thank you so much!

[wanyunyang]

Hi Sunny, may I have one more question for you?

What is the use of Avg(1)?

[sunny_talwar]

Since I am ignoring the selection of Year in the main expression... it will not filter down to just the required year and will show all the years. Avg(1) is used to remove all the unselected year. Avg(1) = 1 for selected year and equals 0 for unselected years

[wanyunyang]

Got it! Thank you Sunny!