Aggregating by ranges

[datanibbler]

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

[vikasmahajan]

Expression

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

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

Vikas

[vikasmahajan]

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

Vikas

[datanibbler]

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

[datanibbler]

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

[datanibbler]

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

[datanibbler]

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

[datanibbler]

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

[vikasmahajan]

Dear Sir

I have not getting any excel file attached ?

Vikas

[datanibbler]

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

Here it comes.