Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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

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 numberOpened by
AB001Jane
AB002John
AB003Mike
AB004John
AB005Jane

[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

Tags (1)
1 Solution

Accepted Solutions

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

My bad, try this:

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

9 Replies

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

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.

Not applicable

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

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()

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

Not applicable

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

qlikexample3.png

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

My bad, try this:

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

Not applicable

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

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()

Hahahahaha, if you insist

Dimension:

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

Expression:

Count(DISTINCT [Incident number])

Not applicable

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

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()

Oops, my bad

Community Browser