Hi,
This is the Demo Data
---------------------------------------------------------------------------------------------
| Channel | Sales | Brand | Dept |
|---|
| Channel1 | 1423 | Samsung | Phone |
| Channel2 | 1223 | Blackberry | PDA |
| Channel3 | 327 | Oppo | Phone |
| Channel4 | 1471 | Nokia | Phone |
| Channel5 | 831 | Sansui | Phone |
| Channel6 | 411 | Toshiba | TV |
| Channel7 | 752 | Apple | Phone |
| Channel8 | 1482 | Micromax | TV |
| Channel9 | 1970 | Le10 | Phone |
| Channel10 | 1952 | Maxima | Watch |
| Channel3 | 354 | Apple | Phone |
| Channel4 | 1323 | Micromax | Tablet |
| Channel5 | 836 | Le10 | Phone |
| Channel6 | 346 | Sansui | Phone |
| Channel7 | 984 | Toshiba | TV |
| Channel8 | 936 | Apple | Phone |
| Channel9 | 930 | Micromax | Phone |
| Channel10 | 540 | Le10 | Phone |
| Channel1 | 434 | Maxima | Phone |
| Channel2 | 381 | Le10 | Phone |
I am trying to
Find out the top 5 channels and accordingly their top brands and top depts .
e.g
TOP 5 Channel
Rank 1
Channel : Channel 10
Top Brand :
Le10
Top Dept:
Phone
Rank 2
Channel : Channel 1
Top Brand :
Samsung
Top Dept:
Phone
.
.
.
likewise
The approach I was following:
* I analysed the data and manually found out the top 5 channels.
* I had used set analysis to find out the top dept and top brand in each of the 5 channels.
* But I had hard coded the top channels, e.g 1st channel as channel 10 2nd as channel 1 likewise . But hard coding is not a viable option as top 5 would keep changing.
* So I found out the top 5 channels using aggr() and rank function.
Aggr(if(Rank(Sum(Sales))=1,Channel),Channel)
Aggr(if(Rank(Sum(Sales))=2,Channel),Channel)
Aggr(if(Rank(Sum(Sales))=3,Channel),Channel)
Aggr(if(Rank(Sum(Sales))=4,Channel),Channel)
Aggr(if(Rank(Sum(Sales))=5,Channel),Channel)
This worked fine. I got the top 5 channels.
* I intended to use them in set analysis in the place of the hard coded values. So, I stored them in variables. But problem is aggr() returns in number format and the channel field is string in db. So when I store the aggr() resut in a var and use set analysis with that var it returns me null because it is probably searching for a number and not getting it.
sum({<Channel={($(Rank1))}>}Sales)
Rank1 is a variable containing : Aggr(if(Rank(Sum(Sales))=1,Channel),Channel)
e.g: the first ranked channel is channel 10 say suppose. But aggr() is returning to the set analysis expression as the numerical equivalent of channel10 maybe. This it cannot seem to find out.
Am I going the right way? Is there a way to typecast the return type of aggr()? Please provide the solution?
