Identify Top 3 in Territory Based on selection with Set Analysis

[Anonymous]

New to Qlikview and need some support with the Set Analysis.

The business scenario is that:

There are 4 regions and each region has some dealers. If user select Dealer A and Dealer A belongs to Region North. Then identify the Top 3 dealers in Region North. And make some calculation with Top 3 dealers and compare to Dealer A. Top 3 should be based on the rank of (Revenue/Qty). Qty = Count(PRODUCTID).

Tried 2 solutions but they did not work:

Solution 1:

Create a variable Top_3_Dealers.

Make a aggregation of Revenue/Qty for each dealer. And rank based on the result. if rank <= 3, concatenate the dealer ID together. Expected result: Top_3_Dealers = 'B', 'C', 'D'

=mid(concat({<REGION={$(=MaxString(REGION))}>}if(aggr({<REGION={$(=MaxString(REGION))}>}rank(sum({<REGION={$(=MaxString(REGION))}>}TOTAL_REVENUE)/count({<REGION={$(=MaxString(REGION))}>}PRODUCTID)), DEALERID)<=3, chr(39) & DEALERID & chr(39) & ',')),1, Len(concat({<REGION={$(=MaxString(REGION))}>}if(aggr({<REGION={$(=MaxString(REGION))}>}rank(sum({<REGION={$(=MaxString(REGION))}>}TOTAL_REVENUE)/count({<REGION={$(=MaxString(REGION))}>}PRODUCTID)), DEALERID)<=3, chr(39) & DEALERID & chr(39) & ','))) - 1 )

And then calculate the weighted average of the Top 3 dealers:

=sum({<DEALERID={$(=Top_3_Dealers)}>}TOTAL_REVENUE)

When only REGION is selected and DEALERID is not selected, it would work as expected. However if DEALERID is selected, the Top_3_Dealers always display the selected dealer.

Solution 2:

Create a variable CurrentRegion, the definition is:

=MaxString(REGION)

Create a variable Top_3_Dealers similar to solution 1 but replace the MaxString(REGION) with

=mid(concat({<REGION={$(=CurrentRegion))}>}if(aggr({<REGION={$(=CurrentRegion)}>}rank(sum({<REGION={$(=CurrentRegion)}>}TOTAL_REVENUE)/count({<REGION={$(=CurrentRegion)}>}PRODUCTID)), DEALERID)<=3, chr(39) & DEALERID & chr(39) & ',')),1, Len(concat({<REGION={$(=MaxString(REGION))}>}if(aggr({<REGION={$(=CurrentRegion)}>}rank(sum({<REGION={$(=CurrentRegion)}>}TOTAL_REVENUE)/count({<REGION={$(=CurrentRegion)}>}PRODUCTID)), DEALERID)<=3, chr(39) & DEALERID & chr(39) & ','))) - 1 )

Same problem.

Could anybody help? Cannot upload the qvw file because of company regulations. Probably just use every simple aggregation to explain the idea(e.g. use sum(sales) as the ranking basis).

The set analysis worked when I was doing the previous month calculation:

=aggr(sum({<RETAIL_MONTH={$(=max(RETAIL_MONTH)-1)}>}TOTAL_REVENUE)/count({<RETAIL_MONTH={$(=max(RETAIL_MONTH)-1)}>}PRODUCTID), DEALERID)

But why it's not working here?

Thanks a lot in advance for your help!

Jason

[Gysbert_Wassenaar]

Try something like this:

concat({<DEARERID={"=Rank(Sum({<REGION=P(REGION)>}TOTAL_REVENUE)/COUNT({<REGION=P(REGION)>}) PRODUCTID )<=3"}, REGION=P(REGION)>} DEALERID, ',' )

Are you sure the quantity is Count(PRODUCTID)? It will count the number of records that contain a PRODUCTID. Does that really equal the quantity?

[Anonymous · Accepted solution]

Thanks a lot!

In actual situation, we have a different field similar to PRODUCTID, like the each item has an identification number. And the field is not easy to understand for others so I changed it to PRODUCTID, however it seems more confusing

I found out a workable solution:

=concat({<DEALERID=, REGION=p(REGION)>}if(aggr(rank(sum({<DEALERID=, REGION=p(REGION)>}TOTAL_REVENUE)/count({<DEALERID=, REGION=p(REGION)>}PRODUCTID)), DEALERID)<=3, chr(39) & DEALERID& chr(39) & ','))