5 Replies Latest reply: Apr 19, 2018 6:35 AM by vishal raina RSS

    Creating aggregated dimension from another dimension

    vishal raina

      Hi all,

      Need urgent help on this one.

      Lets say i have data in the below form :

       

      itemsWeekSales
      a1200
      a2300
      a3400
      b1100
      b2250
      b4250
      c1100
      c2100

       


      From this data i need to create a table which should be like this.

       

      Field RequiredSum(Sales)
      only a900
      only b600
      both a & b850
      neither a nor b200


      The 'Field Required' needs to be aggregated on weeks & items.

      ex, In the required table 'only a' should have weeks which are present only in a i.e.,weeks 1,2,3 of a.

      similar for 'only b'.

      'both a & b' should have weeks which are common in a & b i.e., weeks 1 & 2 of both a & b.

      likewise for 'neither a nor b', weeks without a & b should be present, i.e., c in this case .

       

       

      Currently i'm using the below  expression for the Field Required:

      =if(Aggr(items = 'a',WEEK,items),'only a',

      if(Aggr(items = 'b',WEEK,items),'only b',

      if(Aggr(items = 'a' and items = 'b',WEEK,items),'both a & b',

      if(Aggr(items <> 'a' and items <> 'b',WEEK,items),'neither a nor b'))))

       

      With this i'm able to get only a & only b, but unable to get the other 2.

      Any help on this would be great.