
finding out top X numbers and bottom Y numbers
Leonard Short Jun 24, 2011 2:51 PM (in response to AXON SG)Have you tried using the rank() function? i.e. rank([Licences Used] / [Total Licenses])

Re: finding out top X numbers and bottom Y numbers
AXON SG Jun 26, 2011 9:54 PM (in response to Leonard Short )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

Re: finding out top X numbers and bottom Y numbers
Leonard Short Jun 27, 2011 1:37 PM (in response to AXON SG)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.

Re: finding out top X numbers and bottom Y numbers
AXON SG Jun 27, 2011 9:25 PM (in response to Leonard Short )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)))

Re: finding out top X numbers and bottom Y numbers
Benny Lin Mar 27, 2015 10:56 AM (in response to AXON SG)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)
)





This reply has been hidden. This can happen if the message has been hidden by a moderator, or has been reported as abusive.

Re: finding out top X numbers and bottom Y numbers
konsta klmnsta Aug 25, 2014 5:39 AM (in response to konsta klmnsta)Hello, I would like to know a reason of rejecting my answer
