5 Replies Latest reply on Apr 8, 2013 10:10 AM by Joshua Milligan

    Replication of SumIf Function

    Tony Gruebner

      Hi,

       

      I've looked around for the answer to this but I am only starting to confuse myself.

       

      Here is an example of my dataset:

       

      Month | Value

      1 | 200

      2 | 100

      1 | 150

      e.t.c

       

      I am trying to write a calculated field that calculates the sum of the value for the previous month. For example, if I was doing it in Excel I would do SumIf(MonthColumn,[Month],Value). However I can't see how to do this in Tableau.

       

      Thanks in advance for your help

        • 1. Re: Replication of SumIf Function
          Joshua Milligan

          Tony,

           

          The calculation would probably look something like this:

           

          SUM(

          IF Month = 1 THEN Value END

          )

           

          Tableau calculations are very flexible.  You can embed some sophisticated logic, other calculations, and parameters.  You can do calculations at a row level, an aggregate level, or even a result table level.  It is definitely a different paradigm than Excel, but I think once you get into it you'll really love it!

           

          If the above calculation isn't quite what you are looking for, please let me know and possibly provide some additional data.  Thanks!

           

          Regards,

          Joshua

          • 2. Re: Replication of SumIf Function
            Tony Gruebner

            Hi Joshua,

             

            Thanks for the answer.

             

            I can't quite get this formula to work. What I am trying to do is get the previous month so I should have stated that the SumIf function in excel would be:

            SumIf(MonthColumn,[Month] - 1,Value).

             

            I tried to put the following formula into Tableau:

            Sum(If [Month] = [Month] - 1 then [Value] else 0 end)

            However, this obviously doesn't work as the month can't be month minus 1

             

            Any suggestions.

             

            Thanks

            Tony

            • 3. Re: Replication of SumIf Function
              Joshua Milligan

              Tony,

               

              In that case, try something like:

               

              LOOKUP(SUM([Value]), -1)

               

              It will depend a little on how your ultimate view is structured, but at the simplest it would look like this:

              Capture.PNG

              • 4. Re: Replication of SumIf Function
                Tony Gruebner

                Hi Joshua,

                 

                This worked perfectly, but I have no idea how.

                 

                How did the calculated field know to LookUp the month field where there is no mention of the month field in the calculation?

                • 5. Re: Replication of SumIf Function
                  Joshua Milligan

                  Tony,

                   

                  LOOKUP is a table calculation function.  If you right click the field on the Measure Values shelf you can select the "Edit Table Calculation" option to see all the different options for table calculations.  In this example, I left the default of "Compute Using: Table Down" which means that Lookup(SUM(value), -1) will lookup the previous value (the -1 offset) going down the table.  In this case, that means it gets the previous month.  You could much more explicitly define the table calculation to work that way (by default or in this particular view).

                   

                  This thread has some good information on table calcs: http://community.tableau.com/message/202808#202808

                   

                  Regards,

                  Joshua