Top 10 In Pivot

[#1]

Hi,

I know this has been asked and answered few times.I am having a pivot table with Dimensions as Customer Name and Month Name.

Expression as:

=(Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field))))

Now on the basis of these counts I want to show Top 10 customers.

Please help.

Thanks

[jagan]

Hi Ankit,

Try Rank() in dimension, check below expression as Calculated Dimension or use Customer as Dimension and use below as expression

=if(aggr(rank((Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field)))),4,0),P_ID) <= 10, CustomerDimensionName)

Note : Replace CustomerDimensionName with your actual dimension name.

Regards,

Jagan.

[PradeepReddy]

In Properties-->dimension Limits--> Restrict the data to Top 10

[#4]

Hi Pradeep,

I am using a pivot table and i am not getting dimension Limits option.

Any other way?

Thanks

[#5]

Hi Jagan,

I have tried this and unfortunately its not working.

Please suggest any other option.

Thanks

[SergeyMak]

Hi Ankit,

If Rank() doesn't work - Please share your app.

[PradeepReddy]

sorry it's my mistake while reading as Pie Chart...

[#8]

Hi, PLease try this

(if(rank((Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size<={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field)))),4,0),P_ID) <= 10, CustomerDimensionName)

[#9]

Hi,

I am using a pivot table with Dimensions as Customer Name and Month Name.

Expression as :

=(Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field))))

+

(Count({$<Year_Date={$(=max(ROAD_YEAR))},[Flag Type]={'Road'},Road_Size={20}>}Distinct(Conditional_Field))+(vTEU)*(count({$<Year_Date={$(=max(ROAD_YEAR))},[Flag Type]={'Road'},Road_Size={40}>}DISTINCT(Conditional_Field))))

Now can you help me with why we are using 'P_ID'.

Can you guide me with that.

Thanks

[jagan]

Hi Ankit,

Try this

=if(aggr(rank((Count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={20}>}Distinct(Conditional_Field))+

(vTEU)*(count({$<Year_Date={$(=max(Rail_Year))},[Flag Type]={'Rail'},Rail_Size={40}>}DISTINCT(Conditional_Field)))),4,0),CustomerDimensionName) <= 10, CustomerDimensionName)

Note : Replace CustomerDimensionName with your actual dimension name.

Hope it helps you.

Regards,

Jagan.