topic Re: Aggregating by ranges in QlikView

Aggregating by ranges

datanibbler — Mon, 10 Jun 2013 07:32:08 GMT

Hi,

I would like to display data by categories (such as the book "QlikView 11 for Developers" explains in a chapter "nested aggregation with aggr()).

Background: In that chapter, the dimension is the nr. of flights a carrier serves and the formula is the nr. and name(s) of the respective carrier(s).

I would like to do this a little different: We have about 850 employees and I want to display the nr. of overtime-hrs. per employee - but there might be up to 850 distinct values in the "nr. of overtime hrs" part - probably less, but even 100 is too much, making the display very confusing.

Instead, I want to build categories like "1-10 hrs.", "11 to 20 hrs." etc.

Can anyone tell me how I have to modify the aggr() function to accomplish that?

Thanks a lot!

Best regards,

DataNibbler

Re: Aggregating by ranges

vikasmahajan — Mon, 10 Jun 2013 07:59:30 GMT

Expression

AGGR(RANK(SUM(VALUE)),MONTH,DIM)

Here I use month and dim 2 dimensions chage as per your requirement.

Vikas

Re: Aggregating by ranges

vikasmahajan — Mon, 10 Jun 2013 08:00:20 GMT

Request you to create small demo and attach to help you in better. what exact you want

Vikas

Re: Aggregating by ranges

datanibbler — Mon, 10 Jun 2013 09:20:14 GMT

Hi Vikas,

creating demo files is always a fickle task in my case since all the data I'm dealing with is sensitive, all the more this time as I'm handling personell data. Also, it takes time which I never have. Still, I have made a small Excel file for you that will at least show you what I want to see at the end of it.

In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

Best regards,

DataNibbler

Re: Aggregating by ranges

datanibbler — Mon, 10 Jun 2013 09:20:24 GMT

Hi Vikas,

In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

Best regards,

DataNibbler

Re: Aggregating by ranges

datanibbler — Mon, 10 Jun 2013 09:20:38 GMT

Hi Vikas,

In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

Best regards,

DataNibbler

Re: Aggregating by ranges

datanibbler — Mon, 10 Jun 2013 09:42:14 GMT

Hi Vikas,

In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

Best regards,

DataNibbler

Re: Aggregating by ranges

datanibbler — Mon, 10 Jun 2013 09:42:44 GMT

Hi Vikas,

In addition to this diagram, I will need some possibility to drill down into one category and see the actual personell_numbers so that the respective employees can be identified.

The formula is not difficult - a sum(distinct count(personell_nr)) will do I guess. The difficult part is the dimension.

I have to load all records anyway, so I guess I can easily attach a table_diagram with the details (the individual personell_numbers).

Best regards,

DataNibbler

Re: Aggregating by ranges

vikasmahajan — Mon, 10 Jun 2013 10:11:09 GMT

Dear Sir

I have not getting any excel file attached ?

Vikas

Re: Aggregating by ranges

datanibbler — Mon, 10 Jun 2013 10:46:55 GMT

Oops - you're right. Sorry, I was having some trouble sending my post and I forgot.

Here it comes.