0 Replies Latest reply: May 24, 2016 3:35 PM by JISHNU MUKHERJEE

# Channels with top 5 sales and their top brands and departments

Hi,

This is the Demo Data

---------------------------------------------------------------------------------------------

ChannelSalesBrandDept
Channel11423SamsungPhone
Channel21223BlackberryPDA
Channel3327OppoPhone
Channel41471NokiaPhone
Channel5831SansuiPhone
Channel6411ToshibaTV
Channel7752ApplePhone
Channel81482MicromaxTV
Channel91970Le10Phone
Channel101952MaximaWatch
Channel3354ApplePhone
Channel41323MicromaxTablet
Channel5836Le10Phone
Channel6346SansuiPhone
Channel7984ToshibaTV
Channel8936ApplePhone
Channel9930MicromaxPhone
Channel10540Le10Phone
Channel1434MaximaPhone
Channel2381Le10Phone

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?