4 Replies Latest reply on Nov 19, 2018 11:04 AM by Xi Liu

    Calculation for sum value by different names

    Xi Liu

      Hi, My data is like

       

      "Month"                  :  Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   

      "Actual Number"    :                               3        6       8     10      12    13     15

      "Forecast Number":   2       3       4        5       7        6

       

      I'd like to create a calculated field  to show Actual number if there is, or there is no actual number show forecast number, so it would be

       

      "Month"                      :  Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   

      "Calculated Number" :     2      3       4       3        6       8     10      12    13     15

       

      My calculated field is:

       

      IF CONTAINS([Name],"Actual") AND NOT ISNULL([Value])

      THEN [Value]

      ELSEIF CONTAINS([Name],"Forecast") AND NOT ISNULL([Value])

      THEN [Value]

      ELSE

      NULL

      END

       

      And the result is the following, which is there are both it sums them up, not what I want:

       

      "Month"                      :  Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   

      "Calculated Number" :     2      3       4       8      13     14     10      12    13     15

       

      So please help me with the calculation to get the answer I want, thanks!