Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Top 15 without Dimension limitation

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!

1 Solution

Accepted Solutions
Highlighted

Re: Top 15 without Dimension limitation

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

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

4 Replies
Highlighted

Re: Top 15 without Dimension limitation

Hi,

Use Rank()

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Not applicable

Re: Top 15 without Dimension limitation

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.   

Highlighted

Re: Top 15 without Dimension limitation

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

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

Highlighted
Not applicable

Re: Top 15 without Dimension limitation

Thanks. That was the problem.

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