Limit results in a pivot table based on expression, sort by different dimension

[#1]

I have looked at all the LIMIT discussions and can't find what I am looking for, or at least can't get any of them to work...

See below for pivot table example. There are thousands of records in this table, and I only want to see the top (worst) 50 or 100 based on the MPG. So, of all the records, I want the table limited to the worst 50 or 100 MPG values. But, I need the table sorted by the VEHICLEMGR/FLEET field

Here is the original report I am trying to mimic.

Let me know if I need to clarify anything.. Thank you.

[johnw]

I'm thinking it might work if you replace the Vehicle dimension with a calculated dimension like this:

aggr(if(rank(Miles/Gallons)<=50,Vehicle),Vehicle)

So what this does is basically say, "for each Vehicle, calculate Miles/Gallons, and then rank that against all other Vehicles. If the rank is <=50 (the worst MPGs), then return the Vehicle, else don't return anything." The result, assuming I got everything correct, should be that the expression returns the 50 worst vehicles by MPG. Since those are the only Vehicles that would then be included in your chart, you should then be free to sort your chart however you wish.

Performance might be an issue. Calculated dimensions tend to be slow if you have large data sets.

[#3]

Thanks for the response John... I am a lot closer now, but am still having a problem. I used your statement but get the BEST 50 instead of the WORST. Through experimenting with the rank value, I apparently don't understand how it works... rank=1 gives me the single highest value. rank=2 gives me the second highest, and so forth. rank<=2 gives me the first two records I retrieved above. So, rank<=50 gives me the highest 50. what should I set rank to in order to get the bottom 50?

[#4]

rank>=50 gives all the ranks outside the TOP 50, but not the BOTTOM 50, unless there were only 100 records, which there are not. Does a count of records need to be incorporated somewhere?

[johnw · Accepted solution]

Ah, my oops, but easily fixed. To get the worst 50, just flip the sign:

aggr(if(rank(-Miles/Gallons)<=50,Vehicle),Vehicle)

Or rank the Gallons per Mile instead of the Miles per Gallon (which is probably more clear than flipping the sign):

aggr(if(rank(Gallons/Miles)<=50,Vehicle),Vehicle)

[#6]

That did it.. I switched the expression and got what I was looking for.

Thank You!

[#7]

Hi guys,

Have same issue, I resolved it too by the following script:

=if(aggr(rank(sum(JC.NetBillingActual) + sum(JC.NetBillingForecast)),JC.CUST_NAME) <=10 , JC.CUST_NAME , Null())

The problem I have is that I can have problems creating the lowest 10 JC.CUST_NAME. I tried the following but it did not work:

=if(aggr(rank(-sum(JC.NetBillingActual) - sum(JC.NetBillingForecast)),JC.CUST_NAME) <=10 , JC.CUST_NAME , Null())

Could someone please look what I did wrong.

Regards,

Aissam