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

Replication of SumIf Function

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.

• 1. Re: Replication of SumIf Function

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

Hi Joshua,

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

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: • 4. Re: Replication of SumIf Function

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

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