Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

finding out top X numbers and bottom Y numbers

hi guys

http://i.imgur.com/m6aXy.jpg

the above screenshot is the scrambled version of my table

i would like to find out the top X utlized and bottom Y underutilized of linceses grouped by the license group and site

how could i go about doing that?

6 Replies
Not applicable
Author

Have you tried using the rank() function? i.e. rank([Licences Used] / [Total Licenses])

Not applicable
Author

hi leonard thanks for your reply,

assuming i wanna find out the top/bottom 5 utilizations...

i currently have a chart with a drill down group (Site+License Group)

as for the expression,

=(if(aggr(Rank(aggr(

sum(LIC_USED)

/sum(TOTAL_LIC)

, LICENSE_GROUP, Site)), LICENSE_GROUP)<= 5 or

aggr(Rank(aggr(

sum(LIC_USED)

/sum(TOTAL_LIC)

, LICENSE_GROUP, cSite)), LICENSE_GROUP) >=

(Max(total aggr(Rank(aggr(

sum(LIC_USED)

/sum(TOTAL_LIC)

, LICENSE_GROUP, Site)), LICENSE_GROUP))-5)

, sum(LIC_USED)

/sum(TOTAL_LIC)))

but the chart doesnt seem to be working properly as i wanna show the utilizations for each site

when they drill down the site, it should show me the top/bottom 5 license groups of the respective sites

i think my expression needs some adjustments but i cant figure out what...

im unable to attach/upload anything right now because of some restrictions in my current location

Not applicable
Author

My initial thought here is that you are trying to make this way too complicated for yourself. Also I have run into several instances where the AGGR function doesn't work at all depending on which version of QV you are using so I tend to avoid using it. Plus it is a memory hog.

If you use rank([Licences Used] / [Total Licenses]) in the table above you will get the utilization ranking for each date at each site.

Change the above to rank(sum([Licences Used]) / sum([Total Licenses])) it will give you the utilization rank across all of your dimensions.

If you use a straight table for your chart type you can then go into the presentation tab and tell it to only display 5 rows and group all the others (or not show them at all), then just sort the table ascending by the rank to get the top 5, sort it descending to get the bottom five.

If you need to use something other than a straight table then we will likely need to get into a bit of set analysis.

Not applicable
Author

i have modified my expression to the following with 2 seperate charts instead of a single chart showing the top X and bottom Y and to simply show the top X and bottom Y across all sites instead of respective sites,

=(if(aggr(Rank(aggr(

sum(LIC_USED)

/sum(TOTAL_LIC)

, LICENSE_GROUP))

, LICENSE_GROUP)<= 10

, sum(LIC_USED)

/sum(TOTAL_LIC)))

=(if(aggr(Rank(aggr(

sum(LIC_USED)

/sum(TOTAL_LIC)

, LICENSE_GROUP))

, LICENSE_GROUP) >=

(Max(total aggr(Rank(aggr(

sum(LIC_USED)

/sum(TOTAL_LIC)

, LICENSE_GROUP))

, LICENSE_GROUP))- 10)

, sum(LIC_USED)

/sum(TOTAL_LIC)))

Anonymous
Not applicable
Author

Hello, I would like to know a reason of rejecting my answer

Anonymous
Not applicable
Author

I think the following two expressions would have been correct.

=if(Rank(

sum(LIC_USED)

/sum(TOTAL_LIC)

)<= 10

, sum(LIC_USED)

/sum(TOTAL_LIC)

)

=if(Rank(

sum(LIC_USED)

/sum(TOTAL_LIC)

) >

Max(total aggr(Rank(aggr(

sum(LIC_USED)

/sum(TOTAL_LIC)

, LICENSE_GROUP))

, LICENSE_GROUP))- 10

, sum(LIC_USED)

/sum(TOTAL_LIC)

)