Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thank you!! That worked perfectly.
Great, I am glad I was able to help
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)
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
Superb Sunny..
Or we can say that Avg(1) should read as Avg({$}1) which apply the current selection.
Yup, exactly
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...