Calculated Dimension

[upaliwije]

Hi Friends,

I have created a calculated Dimension with following command in my pivot table

=If(Aggr(Rank(Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/sUM({<VEHICLE_TYPE={'CAR/JEEP'}>}POLNos))>5, [MAKE])<=10, [MAKE], 'Others')

But It does no work properly.

My requirement is to Show 10 records of Vehicle Make where the value of the  following condition is more than 5

Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/sUM({<VEHICLE_TYPE={'CAR/JEEP'}>}POLNos))>5

Kindly help me to get the correct syntax please.

[sunny_talwar]

May be this

=Aggr(
 If(
  Rank(
   Sum({<MAKE = {"=Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/Sum({<VEHICLE_TYPE = {'CAR/JEEP'}>} POLNos) > 5"}, VEHICLE_TYPE = {'CAR/JEEP'}>} CLMPCNT)
/
   Sum({<MAKE = {"=Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/Sum({<VEHICLE_TYPE = {'CAR/JEEP'}>} POLNos) > 5"}, VEHICLE_TYPE = {'CAR/JEEP'}>}POLNos)
  ) <= 10
 , [MAKE], 'Others')
, [MAKE])

[upaliwije]

Thanks Sunny

I applied your syntax but it does not give required out put 

What I want i some thing similar to below output

Pls help

[upaliwije]

[sunny_talwar]

Would you be able to share a sample so that we can see the issue?

[upaliwije]

Dear Sunny,

This is my sample  Appreciate your help 

[sunny_talwar]

It seems that your expression =Sum({<VEHICLE_TYPE={'CAR/JEEP'}>}CLMPCNT)/Sum({<VEHICLE_TYPE = {'CAR/JEEP'}>} POLNos) never gets above 1... look at the image below

Now this might be just for the sample file you have shared. So, in order to make the condition meet, I changed the condition from >5 to > 0.5... and I see this

[upaliwije]

Thanks sunny ,

But in my full data set scenario is as shown below 

Pls suggest solution for it. I also want this to be sorted in descending  order with 'Others' shown as last item please

[sunny_talwar]

Based on the data provided in the sample app... what is the expected output?

[upaliwije]

Expected out put  is there for the sample data  but it has to be sorted descending order. How ever when It applies my full data set  it wont work.