topic Re: Qlik Sense - Histogram - using class() and aggr() in App Development

Qlik Sense - Histogram - using class() and aggr()

Fri, 05 Aug 2016 09:36:14 GMT

Hi,

I would like to create a histogram. I have seen some examples on discussion forums using the class() and aggr() functions by they were not very clear and I am struggling to get it right.

My data (of relevance) is as in the following format:

Incident number	Opened by
AB001	Jane
AB002	John
AB003	Mike
AB004	John
AB005	Jane

[Incident number] is an ID field and I perform Count([Incident number]) to tally the number of incidents opened by each person.

I am trying to get a histogram that will have bins for Count([Incident number]) of the X-axis and the number of people (from the [Opened by] field) that fall in each bin on the y-axis.

If possible, I would also like the bin widths to be generated dynamically ans other field options may be selected that would filter the data used in aggregation.

Please let me know if this can be obtained using the class and aggr functions.

Kind regards,

Conor

Re: Qlik Sense - Histogram - using class() and aggr()

sunny_talwar — Fri, 05 Aug 2016 09:57:11 GMT

Bin width can be dynamic, but you would atleast need to know how many bins you want, right? May be try along these lines:

Dimension:

Aggr(Class(Count([Incident number]), Round(Count([Incident number])/5)), [Opened by])

Expression:

Count([Incident number])

Here I assumed that you need 5 bins, but you can change that number dynamically using a user input into a variable I guess.

Re: Qlik Sense - Histogram - using class() and aggr()

Fri, 05 Aug 2016 10:06:25 GMT

Hi Sunny,

Thanks for this. It's a lot closer to what I want.

I have changed the measure to Count(distinct [opened by]) as I'm looking to count the number of people for whom their total is in that bin.

The only problem is with the bin size. Using the method you have provided the bin widths are not of equal width in terms of the Count([Incident number]) range.

So I would like bins like: 1-20, 21-40, 41-60 etc. with these numbers based on Count([Incident number]).

Re: Qlik Sense - Histogram - using class() and aggr()

sunny_talwar — Fri, 05 Aug 2016 11:08:16 GMT

Really the bins are not equal? Would you be able to share a screenshot of how the bins look?

Re: Qlik Sense - Histogram - using class() and aggr()

Fri, 05 Aug 2016 12:38:08 GMT

Re: Qlik Sense - Histogram - using class() and aggr()

sunny_talwar — Fri, 05 Aug 2016 13:20:41 GMT

My bad, try this:

Aggr(Class(Count([Incident number]), Round(Count(TOTAL [Incident number])/5)), [Opened by])

Re: Qlik Sense - Histogram - using class() and aggr()

Fri, 05 Aug 2016 15:13:18 GMT

Hi Sunny,

Thank you so much. This is working now.

From what I understand the number (5 in this case) that you are dividing by seems to set the bin width in the follow way: bin width = total count of all / 5

So I made my bin widths smaller by making this a larger number.

Do you want to post one more reply with the dimension as you last listed it and the measure as Count(distinct [opened by]) and I will mark it as the correct answer?

Thank you again!

Re: Qlik Sense - Histogram - using class() and aggr()

sunny_talwar — Fri, 05 Aug 2016 15:28:56 GMT

Hahahahaha, if you insist

Dimension:

Aggr(Class(Count([Incident number]), Round(Count(TOTAL [Incident number])/5)), [Opened by])

Expression:

Count(DISTINCT [Incident number])

Re: Qlik Sense - Histogram - using class() and aggr()

Fri, 05 Aug 2016 15:59:07 GMT

That should be:

Dimension:

Aggr(Class(Count([Incident number]), Round(Count(TOTAL [Incident number])/5)), )

Expression:

Count(DISTINCT [Opened by])

Re: Qlik Sense - Histogram - using class() and aggr()

sunny_talwar — Fri, 05 Aug 2016 16:34:54 GMT

Oops, my bad