3 Replies Latest reply: Mar 1, 2013 5:18 PM by whiteline _ RSS

    if statement help

      I have an IF statement that isnt working.  I am trying to use this if statement in my dimension. 

       

      I want my dimension to show buckets of Sales per Agent.  I want to have categories :

       

      0-2

      3-4

      5-6

      7+

       

      Sales per Agent.

       

      =if(aggr(if(count([eAppSaleMetric])<=2, 1, 0), [Agent Name],dual('0-2',1),
      if(aggr(if(count([eAppSaleMetric])>=3 and count([eAppSaleMetric])<=4, 1, 0), [Agent Name], dual('3-4',2),

       

      if(aggr(if(count([eAppSaleMetric])>=5 and count([eAppSaleMetric])<=6, 1, 0), [Agent Name, dual('5-6',3),

       

      if(aggr(if(count([eAppSaleMetric])>=7, 1, 0), [Agent Name] ,   dual('7+',4)))))))))

       

      Any help would be greatly appreciated.  Thanks.

        • Re: if statement help
          whiteline _

          Hi.

           

          You sould use aggr and ifs properly:

          =aggr(if(count([eAppSaleMetric])<=2, 1, 0),

                      dual('0-2',1),

                      if(count([eAppSaleMetric])>=3 and count([eAppSaleMetric])<=4, 1, 0),

                         dual('3-4',2),

                         '...'))

                    ,[Agent Name])

           

          So that you iterate with aggr over each [Agent Name] classifing it along the count([eAppSaleMetric]) value (returning the class).

           

          Hope this helps.

           

          upd:

          =aggr(if(count([eAppSaleMetric])<=2, dual('0-2',1),

                      if(count([eAppSaleMetric])<=4, dual('3-4',2), '...'))

                    ,[Agent Name])

            • Re: if statement help

              Ok, so I corrected my Aggr IF statement and I am still having issues...any suggestions.  Thank you.

               

               

               

              aggr(if(count([eAppSaleMetric])<=2, 1, 0), dual('0-2',1),

               

              if(count([eAppSaleMetric])>=3 and count([eAppSaleMetric])<=4, 1, 0), dual('3-4',2),

               

              if(count([eAppSaleMetric])>=5 and count([eAppSaleMetric])<=6, 1, 0), dual('5-6',3),

               

              if(count([eAppSaleMetric])>=7, 1, 0), [Agent Name] ,   dual('7+',4))

                • Re: if statement help
                  whiteline _

                  Oh... you've corrected them even better than me ))

                   

                  I think this one is what you're looking for:

                  =aggr(if(count([eAppSaleMetric])<=2, dual('0-2',1),

                         if(count([eAppSaleMetric])<=4, dual('3-4',2),

                         if(count([eAppSaleMetric])<=6, dual('5-6',3), dual('7+',4)))), [Agent Name])

                   

                  Sorry, didn't noticed the logic of your ifs yesterday, just mechanically corrected aggr() syntax.