Two level of aggregation: Require help

[#1]

I need to get Top 5 OtherBrandOwned and for those 5 top brands what are the Top 1 OtherModelOwned.

Data something like:

Output:

A3 Purchased - 3903

I am using text boxes to calculate these as we also need ratio of each value by Car Purchased -  A3/A4/A6 etc.

I am able to get Top 5 OtherBrandOwned by using Rank() with Aggr() as shown above.

However, I am unable to get Top 1 Models for each of these Brands correctly.

For 4th Brand - Hyundai, I am getting model as 'Innova' which is incorrect.It should be Hyundai and Verna.

For 5th Brand - Maruti, I am getting model as 'Verna' which is incorrect.It should be Maruti and Swift.

How to customize the expression to get correct values for top 1 model. Please help.

Message was edited by: Nidhi Gupta

[rahulpawarb]

Hello Nidhi,

Could you please share the application with sample data? This will help us to provide you required inputs.

Regards!

Rahul

[#3]

Attaching sample qv file.

[rahulpawarb · Accepted solution]

Hello Nidhi,

Thank you for sharing the application with sample data.

To fix this issue I have created variables to hold VehicleBrandName at various levels considering VehicleModelName and used those variables in the expressions.

Please refer updated application file attached herewith.

Regards!

Rahul

[#5]

Hi Rahul,

It was a challenge for me to put aggr() inside an aggr() function.

Your solution is spot on !! Thanks

[rahulpawarb]

Thank you

Rahul