topic Re: Cumulative chart details in QlikView

Cumulative chart details

reshmakala — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Cumulative chart details

sunny_talwar — Wed, 11 May 2016 20:02:13 GMT

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

Re: Cumulative chart details

reshmakala — Wed, 11 May 2016 20:18:32 GMT

Thank you!! That worked perfectly.

Re: Cumulative chart details

sunny_talwar — Wed, 11 May 2016 20:19:18 GMT

Great, I am glad I was able to help

Re: Cumulative chart details

trdandamudi — Wed, 11 May 2016 20:24:54 GMT

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)

Re: Cumulative chart details

sunny_talwar — Wed, 11 May 2016 20:29:59 GMT

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‌

Re: Cumulative chart details

Saravanan_Desingh — Wed, 11 May 2016 20:40:20 GMT

Superb Sunny..

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

Re: Cumulative chart details

sunny_talwar — Wed, 11 May 2016 20:41:20 GMT

Yup, exactly

Re: Cumulative chart details

trdandamudi — Wed, 11 May 2016 20:42:11 GMT

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...