Showing lowest top 30

[#1]

Hi all,

Im tyring to make a pivot table that will show me the lowest 30 performances. Now I already applied this concept a few times but this time it doens't seem to work.

Expressions normally give me the results and I want to grade on the performance grade. So I added this in a drill down menu and then into the dimension:

=if(aggr(rank((((-sum({<Year={'$(=max(Year))'}>} DistanceCruiseControl)/sum({<Year={'$(=max(Year))'}>} Eco.TotalDistance))*1000*0.3)+
((sum({<Year={'$(=max(Year))'}>} DistanceCoasting)/sum({<Year={'$(=max(Year))'}>} TotalDistance))*1000*0.3)+
((sum({<Year={'$(=max(Year))'}>} DistanceBraking)/sum({<Year={'$(=max(Year))'}>} TotalDistance*1000))*1000*0.1)+
((sum({<Year={'$(=max(Year))'}>} GreenSpotResult_DistanceGreenSpot)/sum({<Year={'$(=max(Year))'}>} TotalDistance))*1000*0.3))

/

vPerformanceTargetFormula),DriverName) <=$(vTopShow),,'Other Drivers')

I get this image:

Could someone tell me where it is going wrong?

Kind regards

Vincent

[Gysbert_Wassenaar · Accepted solution]

You need to specify a value for the True branch of the if statement too. It's not optional.

Try:

=if(aggr(rank((((-sum({<Year={'$(=max(Year))'}>} DistanceCruiseControl)/sum({<Year={'$(=max(Year))'}>} Eco.TotalDistance))*1000*0.3)+ 

((sum({<Year={'$(=max(Year))'}>} DistanceCoasting)/sum({<Year={'$(=max(Year))'}>} TotalDistance))*1000*0.3)+

((sum({<Year={'$(=max(Year))'}>} DistanceBraking)/sum({<Year={'$(=max(Year))'}>} TotalDistance*1000))*1000*0.1)+

((sum({<Year={'$(=max(Year))'}>} GreenSpotResult_DistanceGreenSpot)/sum({<Year={'$(=max(Year))'}>} TotalDistance))*1000*0.3))

/

vPerformanceTargetFormula),DriverName) <=$(vTopShow),'','Other Drivers')

[#3]

Thank you Gysbert!