Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Expression
AGGR(RANK(SUM(VALUE)),MONTH,DIM)
Here I use month and dim 2 dimensions chage as per your requirement.
Vikas
Request you to create small demo and attach to help you in better. what exact you want
Vikas
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
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
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
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
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
Dear Sir
I have not getting any excel file attached ?
Vikas
Oops - you're right. Sorry, I was having some trouble sending my post and I forgot.
Here it comes.