Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
Contributor

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

Re: Bar Chart - accumulation

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

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

7 Replies

Re: Bar Chart - accumulation

May be try this expression:

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

wanyunyang
Contributor

Re: Bar Chart - accumulation

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.

Re: Bar Chart - accumulation

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

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

wanyunyang
Contributor

Re: Bar Chart - accumulation

It works! Thank you so much!

wanyunyang
Contributor

Re: Bar Chart - accumulation

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

What is the use of Avg(1)?

Re: Bar Chart - accumulation

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
Contributor

Re: Bar Chart - accumulation

Got it! Thank you Sunny!

Community Browser