Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi guys
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?
Have you tried using the rank() function? i.e. rank([Licences Used] / [Total Licenses])
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
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.
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)))
Hello, I would like to know a reason of rejecting my answer
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)
)