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

[antoniotiman]

Hi,

Try (Calulated Dimension)

Aggr(If(Rank(Expression) <= 10,Customer),Customer)

REgards,

Antonio

[#3]

Hi Jagan,

It was working, however now my expression is :

=(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)))).

Can you guide me with this?

Thanks

[jagan]

Hi,

Try like 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))))

+

(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)))),4,0),CustomerDimensionName) <= 10, CustomerDimensionName)

Note : Replace CustomerDimensionName with your actual dimension name.

Hope it helps you.

Regards,

Jagan.

[#5]

Hi Jagan,

Everything seems to be working now.One last help.Why its only showing me the Month of 'APR' while updating the data.

Thanks

[jagan]

Hi,

I didn't get you, is your data is there for all the months?  Check data if it is there then try removing the Year_Date={$(=max(Rail_Year))} in set analysis and check.

Regards,

jagan.

[#7]

Hi Jagan,

Yes my data is for all the months and if i remove Year_Date={$(=max(Rail_Year))} from my expression,no data is being displayed.

Thanks

[jagan]

HI,

Attach sample file so that it would be easier to fix it.

Regards,

Jagan.

[#9]

Hi Jagan,

PFA..and my expression will remain same that we discussed before.

Please help me to show Top10 Customer's

Thanks

[SergeyMak]

Hi Ankit,

PFA

I used expression

Sum({<[Customer Name] = {"=Rank(SUM(TEUS))<=10"}>}TEUS)