Top 20, inside Top 20, inside Top 20 report

nico_ilog — Wed, 20 Apr 2016 10:40:52 GMT

Community,

I need your assistance.

How can we create a Pivot / Straight table, that will display Top20, inside Top20, inside Top20.

The logic is this.

I want to see My Top 20 Sales Reps, For each of these Sales Reps i would like to see Each's Top 20 Customers, For each of these Customers, i would like to see the Top 20 Products that they purchase.

ALL is one Graph (i think a pivot will work best).

This is what I've tried, which works.. but i lose formatting.

The expression:

=if(rank(((SUM({$<Stk_ProductName = {'*'}-{'BROKEN PACK CHARGE'},Sales_Year=,SH_TransactionDate={'>=$(vMATTYStart)<=$(vMATTYEnd)'},Sales_Month=,Sales_Day=,Sales_MonthYear=,SH_SellingLoc = {'ULA'},SH_Doc_Index = {'I'}>}(SD_SizeinL))

- SUM({$<Stk_ProductName = {'*'}-{'BROKEN PACK CHARGE'},Sales_Year=,SH_TransactionDate={'>=$(vMATTYStart)<=$(vMATTYEnd)'},Sales_Month=,Sales_Day=,Sales_MonthYear=,SH_SellingLoc = {'ULA'},SH_Doc_Index = {'J'}>}(SD_SizeinL))) / 9)

,1,1) <= 20,((SUM({$<Stk_ProductName = {'*'}-{'BROKEN PACK CHARGE'},Sales_Year=,SH_TransactionDate={'>=$(vMATTYStart)<=$(vMATTYEnd)'},Sales_Month=,Sales_Day=,Sales_MonthYear=,SH_SellingLoc = {'ULA'},SH_Doc_Index = {'I'}>}(SD_SizeinL))

But the logic is, if the rank, after the sum is <= 20, then sum.

I added the RepName, Customer, Product into the Pivot Table. BUT it displays only the top 20, of the dimension next to the expression and then displays ALLLL clients, Not TOP20 only. (see attached)

Any help will be appreciated.

The desired result would be the same as in the Attached, but showing only Top20 Customer, not ALL!!

I did try to use AGGR on the dimension of Customer, but did't work.

Thanks & apologies for the HUGE post!

topic Top 20, inside Top 20, inside Top 20 report in QlikView

Top 20, inside Top 20, inside Top 20 report