Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
In Properties-->dimension Limits--> Restrict the data to Top 10
Hi Pradeep,
I am using a pivot table and i am not getting dimension Limits option.
Any other way?
Thanks
Hi Jagan,
I have tried this and unfortunately its not working.
Please suggest any other option.
Thanks
Hi Ankit,
If Rank() doesn't work - Please share your app.
sorry it's my mistake while reading as Pie Chart...
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)
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
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.