Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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?

Tags (3)
6 Replies
Not applicable

finding out top X numbers and bottom Y numbers

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

Not applicable

Re: finding out top X numbers and bottom Y numbers

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

Re: finding out top X numbers and bottom Y numbers

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

Re: finding out top X numbers and bottom Y numbers

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

konstantin
Contributor III

Re: finding out top X numbers and bottom Y numbers

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

blin
New Contributor II

Re: finding out top X numbers and bottom Y numbers

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)

)