Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Hi,
Use Rank()
You will get so many example for Rank() in Qlikview Community.
Regards
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.
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
Thanks. That was the problem.
I was applying the function to both dimension instead of the Projects one only.