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

    Channels with top 5 sales and their top brands and departments

    JISHNU MUKHERJEE

      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?