topic Limit to top 10% in QlikView

Limit to top 10%

Tue, 20 Jul 2010 00:02:28 GMT

I am using set analysis to collect data for a dimension going back vLowUsageDayBack days from today, which is working well.

sum( {$ < RollupDate = {'>=$(=date(Today()-vLowUsageDaysBack))'} >}
RollupNetworkSessionTotalBytes/ vOneGigaByte )

I want to further limit it such that only the top X% are included. For example if there are 100 and percentage is set to 10% I only want the top 10.

Thanks.

Limit to top 10%

shumailh — Wed, 21 Jul 2010 12:00:37 GMT

Can you share us some sample data to more clarify your requirement?

Regards,
Shumail

Limit to top 10%

Wed, 21 Jul 2010 19:35:34 GMT

Using sum( {$ < RollupDate = {'>=$(=date(Today()-vLowUsageDaysBack))'} >}
RollupNetworkSessionTotalBytes/ vOneGigaByte ) to sum bytes by dimension users I get 100 users in the chart.

U1 - 1,000,000
U2 - 999,000
U3 - 888,000
U5 - 777,000
U5 - 666,000

etc....

U99 - 10
U100 - 1

I only want to display the top 5% in the chart (U1, U2, U3, U4, & U5)

How do I do this?

Limit to top 10%

nathanfurby — Thu, 22 Jul 2010 10:16:42 GMT

Hard to imagine without the data and taking a wild guess here:

I would try to create a variable that works out the actual number of users that need to be returned: Count(Users)/100*percentage required(5 or 10 etc.). Then use round() to bring it up to whole number. You then might be able to use the variable in a Rank expression.

Limit to top 10%

Anonymous — Thu, 22 Jul 2010 10:27:46 GMT

As Nathan says you might want to look at variables and Rank().

Something along the lines of:

if(
rank(sum( {$ < RollupDate = {'>=$(=date(Today()-vLowUsageDaysBack))'} >} RollupNetworkSessionTotalBytes/ vOneGigaByte ))<=$(VARIABLE),
sum( {$ < RollupDate = {'>=$(=date(Today()-vLowUsageDaysBack))'} >} RollupNetworkSessionTotalBytes/ vOneGigaByte )
)

Where you insert a number into the VARIABLE, for example 10 to show the top 10 ranking values.

Limit to top 10%

Thu, 10 Feb 2011 08:58:08 GMT

Hi Johannes can you help me as I am looking for something different.

Lets say my data is like following

Ind PDT Rev

A AB 40

A AC 60

A BC 20

A BD 40

A AD 30

B AB 25

B AC 35

B AD 40

C AC 60

C BC 80

C BD 30

Now I want to make a chart representing only those top PDT which are contributing top 80% of the Rev for particular Ind. Like for Ind A i want only AB, AC and BC to be displayed in the chart