Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wanyunyang
Creator III
Creator III

Bar Chart - accumulation

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! 

1 Solution

Accepted Solutions
sunny_talwar

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

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

View solution in original post

7 Replies
sunny_talwar

May be try this expression:

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

wanyunyang
Creator III
Creator III
Author

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

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

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

wanyunyang
Creator III
Creator III
Author

It works! Thank you so much!

wanyunyang
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Got it! Thank you Sunny!