topic Re: Top 15 without Dimension limitation in QlikView

Top 15 without Dimension limitation

Wed, 04 Feb 2015 09:40:39 GMT

Hi,

I'm currently trying to make a Top 15 but the Dimension Limit won't return what I need.

Currently, it's a straight table with 2 dimensions: [Client] and [Project]

Now the trick is that I need to make a Top 15 for the YTD expression so my expression looks like:

(sum({< Calendar.MonthYear=,[Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Billed_Amount_GBP_Prof)

-sum({< Calendar.MonthYear=,[Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_SalaryCost*Profitability_KPI.Billed_Hours)

-sum({< Calendar.MonthYear=,[Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_OverHeadCost*Profitability_KPI.Billed_Hours)

-sum({< Calendar.MonthYear=,[Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_Ptr_Draw*Profitability_KPI.Billed_Hours))

/

sum({< Calendar.MonthYear=,[Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Profitability_KPI.Billed_Amount_GBP_Prof)

When I use the Dimension limit, It returns more than the First 15 rows that I'm setting.

How can I proceed?

Thanks for you help!

Re: Top 15 without Dimension limitation

PrashantSangle — Wed, 04 Feb 2015 09:42:48 GMT

Hi,

Use Rank()

You will get so many example for Rank() in Qlikview Community.

Regards

Re: Top 15 without Dimension limitation

Wed, 04 Feb 2015 09:48:23 GMT

I tried and altered by Dimensions:

Client:

=if(

aggr(

rank(

(

(sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Billed_Amount_GBP_Prof)

-sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_SalaryCost*Billed_Hours)

-sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_OverHeadCost*Billed_Hours)

-sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_Ptr_Draw*Billed_Hours))

sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Billed_Amount_GBP_Prof)

)

,Client

)<=15 ,Client

)

Project:

=if(

aggr(

rank(

(

(sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Billed_Amount_GBP_Prof)

-sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_SalaryCost*Billed_Hours)

-sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_OverHeadCost*Billed_Hours)

-sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Cost_Ptr_Draw*Billed_Hours))

sum({< Calendar.MonthYear=,Person={'$(vPerson)'} , main_refdate={'$(vSetYTD)'}>}Billed_Amount_GBP_Prof)

)

,Project

)<=15 ,Project

)

This did not work but I'm wondering if it's correct... It actually works but returns less than 15 Projects.

Re: Top 15 without Dimension limitation

PrashantSangle — Wed, 04 Feb 2015 09:55:49 GMT

Hi,

In one chart you can show either top 15 Client or top 15 Project

So choose which one you required. Then write only that dimension with Rank()

and other one is your normal

Regards

Re: Top 15 without Dimension limitation

Wed, 04 Feb 2015 10:15:52 GMT

Thanks. That was the problem.

I was applying the function to both dimension instead of the Projects one only.