topic Re: Two level of aggregation: Require help in QlikView

Two level of aggregation: Require help

Thu, 02 Mar 2017 05:00:24 GMT

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

Re: Two level of aggregation: Require help

rahulpawarb — Thu, 02 Mar 2017 05:12:02 GMT

Hello Nidhi,

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

Regards!

Rahul

Re: Two level of aggregation: Require help

Thu, 02 Mar 2017 06:00:57 GMT

Attaching sample qv file.

Re: Two level of aggregation: Require help

rahulpawarb — Thu, 02 Mar 2017 10:36:41 GMT

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

Re: Two level of aggregation: Require help

Fri, 03 Mar 2017 08:45:12 GMT

Hi Rahul,

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

Your solution is spot on !! Thanks

Re: Two level of aggregation: Require help

rahulpawarb — Fri, 03 Mar 2017 09:10:26 GMT

Thank you

Rahul