5 Replies Latest reply on Oct 26, 2012 3:55 PM by Ahmet Eser

    Sum two if statements

    Ahmet Eser

      Hello,

       

      I have a question that might be too easy to answer I have some data that looks like this:

       

      event
      time
      value
      a12
      b13
      c14
      a23

       

      I want to create a calculated field where I want to get the sum of the values of some specific events for each time instance.

       

      For instance, I thought doing something like this: ' if event=a then value end + if event=b then value end ' and then placing my Calculated Field pill into the Rows shelf and Time pill into the Columns shelf. I also tried the same using something like this: ' if event=a OR event=b then value end ' 

       

      However, neither combination works so far. For instance, I believe that the second combination of the two if statements stops when it detects that event is 'a' and it doesn't proceed to the next part to get the value for the other event which occurred at the same time instance-- but I want to sum the two values up.

       

      I also tried creating different calculated fields with one if statement in each and then another one combining the other calculated fields. I couldn't make it work, but it might be simply I am entering the formula wrong. Would something like this work? Actually, is there an easier way to do this?

       

      Thank you very much!!!

        • 1. Re: Sum two if statements
          Alex Kerin

          Can you give an example of the result you would expect with the data above?

          • 2. Re: Sum two if statements
            Jonathan Drummey

            The IF [event] = "a" OR [event] = "b" THEN [value] END calc will work inside a SUM() when broken down by the time dimension, so I'm not sure what your goal is. See the attached for an example, using the data table you'd posted.

             

            Jonathan

            • 3. Re: Sum two if statements
              Ahmet Eser

              Hi guys,

               

              Thanks a lot for your responses! I think we're on the right track. Jonathan, your workbook seems good, gives me what I want (please see the attached workbook named "what I want"), but when I try to do it in my workbook, it doesn't work.

               

              I think I was able to reproduce the error in my original workbook using the workbook you attached. Here are the steps I follow:

               

              1) Remove the "time" pill from the columns shelf and convert it to Measure

              2) Put it back in the columns shelf and put the calculated field "sum of a or b values" into the rows shelf. Next, uncheck "aggregate measures" from the Analysis menu. What you see is the current status of my real workbook. I want to get the value 5 at time instance 1.

              3) I try to get back to the working state in our workbook (the one named "what I want" by following these steps: Remove "time" from the columns shelf, convert it to dimension and put it back in. However, it doesn't really change and doesn't give me what we had originally. Please see the other workbook (i.e., "What I don't want") for a demonstration of this.

               

              The reason behind this might be the exact same reason why it doesn't work in my workbook either. Maybe, I am missing something very simple.

               

              Thank you again! I immensely appreciate your help.

              • 4. Re: Sum two if statements
                Tracy Rodgers

                Hi Ahmet,

                 

                In order to get the view in the workbook "What I Want," Time needs to a remain a dimension and Aggregate Measures needs to be turned on (from the Analysis menu).

                 

                Hope this helps!

                 

                -Tracy

                1 of 1 people found this helpful
                • 5. Re: Sum two if statements
                  Ahmet Eser

                  I think this resolves everything. Thank you so much guys!

                   

                  --Ahmet