2 Replies Latest reply: Jan 7, 2013 1:51 PM by Michael Solomovich RSS

    Sum of if statements

      Hello;
      i am trying to sum two if statements but no results are returned.
      Any idea why?
      What could be changed.
      the IF's on their own work very well and return the amount expected.
      Summed nothing happen???
      My goal is per week to sum the acitivites with problems looking at certain types of activities and a ratio of hours.
      num#
      (If(sum(If(ActivityType='INEN'OR ActivityType='ISSS'OR ActivityType='IDOA',1,0)
      +
      IF(((Sum ({<ActivityType={'FSRV'} >} _Hours_worked)+Sum ({<ActivityType= {'INEN'}>} _Hours_worked)+Sum ({<ActivityType= {'ISSS'}>} _Hours_worked)+Sum ({<ActivityType= {'IDOA'}>} _Hours_worked))/[Target Inst Hours])
      >1.5,1, 

      IF (((Sum ({<ActivityType={'FSRV'} >} _Hours_worked)+Sum ({<ActivityType= {'INEN'}>} _Hours_worked)+Sum ({<ActivityType= {'ISSS'}>} _Hours_worked)+Sum ({<ActivityType= {'IDOA'}>} _Hours_worked))/[Target Inst Hours])

      <0.3,1,0))

      >0.1,1,0)))

       

      Hope somebody can provide some guidance.

       

      Many thanks in advance.

       

      Kind regards;

       

      Kristel


       


        • Re: Sum of if statements
          Adam Wilson
          I'm not sure what the logic is you are aiming for but I have made it a bit more readable and have highlighted the syntax problem you are having:
          it looks like you have got the sum function mixed up within the if function, possibly because of a misplaced parenthesis
          hopefully you will be able to distinguish what it is you were trying to achieve

           

          num#

          (

              If(

                  sum(

                      If(ActivityType='INEN'OR ActivityType='ISSS'OR ActivityType='IDOA',1,0)

                      +

                      IF(

                          (

                              (

                                  Sum ({<ActivityType={'FSRV'} >} _Hours_worked) +

                                  Sum ({<ActivityType= {'INEN'}>} _Hours_worked) +

                                  Sum ({<ActivityType= {'ISSS'}>} _Hours_worked) +

                                  Sum ({<ActivityType= {'IDOA'}>} _Hours_worked)

                              )

                              /[Target Inst Hours]

                          )

                          >1.5,1,

                              IF (

                                  (

                                      (

                                          Sum ({<ActivityType={'FSRV'} >} _Hours_worked)+

                                          Sum ({<ActivityType= {'INEN'}>} _Hours_worked)+

                                          Sum ({<ActivityType= {'ISSS'}>} _Hours_worked)+

                                          Sum ({<ActivityType= {'IDOA'}>} _Hours_worked)

                                      )

                                      /[Target Inst Hours]

                                  )

                                  <0.3,1,0

                              )

                      )

                  >0.1,1,0

                  )

              )

          )


            • Re: Sum of if statements
              Michael Solomovich

              You can further make it more readable by replacing this:

               

              Sum ({<ActivityType={'FSRV'} >} _Hours_worked)+
              Sum ({<ActivityType= {'INEN'}>} _Hours_worked)+
              Sum ({<ActivityType= {'ISSS'}>} _Hours_worked)+
              Sum ({<ActivityType= {'IDOA'}>} _Hours_worked)

               

              with this:

               

              Sum ({<ActivityType={'FSRV','INEN','ISSS','IDOA'} >} _Hours_worked)