Top 15 without Dimension limitation

[#1]

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!

[PrashantSangle]

Hi,

Use Rank()

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

Regards

[#3]

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.   

[PrashantSangle · Accepted solution]

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

[#5]

Thanks. That was the problem.

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