8 Replies Latest reply: May 22, 2017 10:17 AM by Sunny Talwar RSS

    Create Buckets in Expression

    Phalgun Parvathaneni

      Hello, I have a dataset that is as follows:

       

      Vendor       Sales    PO#

      A              1300        1

      B              2200        2

      A              1400        1

      B              2100        3

      C              2500        4

      A              300          2

      C              900          4

      D              3200        5

      B              2000        3

      D              1500        5

      E              1100        6

      F              3500        7

      E              1500        8

      G              1400        9

      H              600          10

      H              900          10

      G             1200         11


      Now I'd like to create two Buckets based on Sum(Sales): one by Grouped by vendor and the other Grouped by PO#. The buckets should be as follows:

      <5k,5k-50k,50k-100k,100k-250k,250k-500k,500k-1Mn. I tried creating a dimension like this:

       

      Aggr(If(Sum([Sales]) > 0 and Sum([Sales]) < 5000, Dual('5k or Below',1),

          If(Sum([Sales]) < 50000,Dual('5k-50k',2),

              If(Sum([Sales]) < 100000, '50k-100k',Dual('50k-100k',3),

              If(Sum([Sales]) < 250000,'100k-250k',Dual('100k-250k',4),

              If(Sum([Sales]) < 500000, '250k-500k',Dual('250k-500k',5),

              Dual('500k-1Mn',6)))))),[Vendor]) for Vendor Buckets

       

      and

       

      Aggr(If(Sum([Sales]) > 0 and Sum([Sales]) < 5000, Dual('5k or Below',1),

          If(Sum([Sales]) < 50000,Dual('5k-50k',2),

              If(Sum([Sales]) < 100000, '50k-100k',Dual('50k-100k',3),

              If(Sum([Sales]) < 250000,'100k-250k',Dual('100k-250k',4),

              If(Sum([Sales]) < 500000, '250k-500k',Dual('250k-500k',5),

              Dual('500k-1Mn',6)))))),[PO#]) for PO buckets.

       

      But it seems to not work as only one bar with '-' as dimension is being displayed. If you could correct me or give a way to create such buckets, I'd be grateful. TIA!

       

      stalwar1 loveisfail