Cumulative chart details

[reshmakala]

Hi everyone!

I have a bar chart where am counting the number of employees for every year and displaying the cumulative count.

For example: For 2012, I have a cumulative count of 7 which includes employees from its previous years. If I click on the bar at 2012, Is it possible to show the names of all 7 people in the table below instead of employees only to 2012?

Attached is my sample application.

[sunny_talwar · Accepted solution]

Check out the attached

Bottom chart expression:

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

Top chart expression:

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

With no accumulation option

[reshmakala]

Thank you!! That worked perfectly.

[sunny_talwar]

Great, I am glad I was able to help

[trdandamudi]

Sunny,

In the below expression, what is the significance of Avg(1) ?

Top chart expression:

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

[sunny_talwar]

So if you remove Avg(1), you will see that the bar chart will continue to show all the years because of my first expression RangeSum(Above(Count(DISTINCT {1} Name), 0, RowNo())) where I am telling it not filter on any selections. The reason I do this is because I want it to accumulate which it won't do without the one. But now the issue is, I don't want to see all the years. I just want to see the selected year. So in order to do that, I can multiply by Avg(1). Avg(1) will equal to 0 for all the years not selected and will equal to 1 where selected.

Alternative to the above expressions would be something like this:

=If(Count(Name) > 0, RangeSum(Above(Count(DISTINCT {1} Name), 0, RowNo())))

I was introduced to this technique by my Qlik Hero swuehl‌

[Saravanan_Desingh]

Superb Sunny..

Or we can say that Avg(1) should read as Avg({$}1) which apply the current selection.

[sunny_talwar]

Yup, exactly

[trdandamudi]

Brilliant techniques.... I learn a lot from you and Swuehi and please keep up the good work... I always think there is lot to learn...