6 Replies Latest reply: May 29, 2017 5:16 AM by Phalgun Parvathaneni RSS

    Summing negative Values in Buckets using Aggr(If(Sum))

    Phalgun Parvathaneni

      Hello all,

       

      I'm trying to create some buckets with the following expression:

       

      Aggr(If(Sum([Value in USD]) < 0, Dual('<0',1),

           If(Sum([Value in USD]) < 500, Dual('0-500',2),

           If(Sum([Value in USD]) < 2500, Dual('500-2.5k',3),

           If(Sum([Value in USD]) < 5000, Dual('2.5k-5k',4),

           If(Sum([Value in USD]) < 20000, Dual('5k-20k',5),

           If(Sum([Value in USD]) < 50000, Dual('20k-50k',6),

           If(Sum([Value in USD]) < 250000, Dual('50k-250k',7),

           If(Sum([Value in USD]) < 500000, Dual('250k-500k',8),

           If(Sum([Value in USD]) < 1000000, Dual('500k-1Mn',9)

           Dual('>1Mn',10)))))))))), [PO#]).

       

      As you can see, the first bucket needs to sum up all the PO# that have negative values. I have tried using the above expression but all I get is a bar with the label '-'. I think this is because of the negative values in my data. How do I proceed to achieve this? TIA!

       

      stalwar1