1 2 3 Previous Next 30 Replies Latest reply on Jan 30, 2013 6:26 AM by Bruce Casper

# How do I sum only postive numbers in a given column of data?

How do I sum only postive numbers in a given column of data? I am basically trying to replicate the SUMIF function from Excel.

Thanks,

"First Time User"

• ###### 1. Re: How do I sum only postive numbers in a given column of data?

Hey Bruce,

I think you'll want to create a calculated field... (click Analysis > Create Calculated Field...)

You'll want to use an IF statement such that,

IF

[YourData] > 0

THEN

[YourData]

ELSEIF

[YourData] < 0

THEN

0

END

Then just drag your new calculation onto the sheet. Let me know if that works.

1 of 1 people found this helpful
• ###### 2. Re: How do I sum only postive numbers in a given column of data?

Thanks Derek. I would like to add the calculation a step further to show the postive values only. For example, lets assume we have 5 numbers:

10

-5

2

I would want the calculation to display 12 vs. 7 and treat the negative number as a zero.

Thanks,

Bruce

• ###### 3. Re: How do I sum only postive numbers in a given column of data?

Tweaking Derek's formula a bit:

IF

[YourData] >= 0

THEN

"Positive"

ELSEIF

[YourData] < 0

THEN

"Negative"

END

Then, use this new calculated field as a filter, you should be able to filter (and show) the positives only.

• ###### 4. Re: How do I sum only postive numbers in a given column of data?

Or you could just get rid of the ELSEIF part of Derek's statement:

IF [YourData]>=0 THEN [YourData] END

(See attached. Neg & Pos sheet.)

Ey, you really don't have to set up a calculated field to filter for positive numbers, you can do it as a conditional statement instead:

(See sheet Filter Negs) I suspect that when you get up into the millions of rows this will execute faster, but that's something Jonathan will have to tell us.

--Shawn

• ###### 5. Re: How do I sum only postive numbers in a given column of data?

I'm pretty sure the filter condition would work faster, because that would be in the WHERE clause of the query that Tableau issues to the underlying data source, so instead of returning all rows and only summing the positive rows, the query would only be returning the rows where the value is positive and then adding those. This speaks to the general principle that performance will almost always improve by reducing the number of rows that are returned from the data source.

Jonathan

• ###### 6. Re: How do I sum only postive numbers in a given column of data?

Thanks for the insights Jonathan.

--Shawn

• ###### 7. Re: How do I sum only postive numbers in a given column of data?

Shawn,

could repost the Bruce.PNG image?

Thanks,

Bruce

• ###### 8. Re: How do I sum only postive numbers in a given column of data?

Yeah, well I can see the image in Chrome, but obviously you can't. So instead of doing it as an inline, I'm just going to attach it as a file.

--Shawn

• ###### 9. Re: How do I sum only postive numbers in a given column of data?

Still have problems. What I am trying to do is replicate the sumif function from Excel as noted below

(=SUMIF(C2:C68,">0")

Any suggestions?

• ###### 10. Re: How do I sum only postive numbers in a given column of data?

Jonathan,

Do you have any insights?

• ###### 11. Re: How do I sum only postive numbers in a given column of data?

Hi Bruce,

I'm not sure what your question is? Any of the formulas above should work, I set up an example using the 10, -5, 2 example you gave above in the attached, which assumes that you want to show both the positive and negative sums in the same worksheet. This is because filters on non-aggregated fields and regular aggregates are applied in the data source, so a positive filter would only return positive values for the worksheet, and not make it possible to show the negative values.

Jonathan

• ###### 12. Re: How do I sum only postive numbers in a given column of data?

Hi Jonathan,

I am very close but want to introduce another variable which I should have included earlier in my question.

If I had an additional field titled Account and there are  11 orders in total among 8 accounts, how would I arrive at the following the customer total given I net the totals within an account.  I would need to repeat this calculation with additional orders/accounts.

Thanks!

• ###### 13. Re: How do I sum only postive numbers in a given column of data?

Hi Bruce,

I don't understand the second paragraph. Are Customer and Account the same thing?  Also, when you sat you want to repeat the calculation, do you have multiple accounts in the same view?

What would be most helpful here so I can hive you a useful answer is some sample data, and mockup of your desired output. For example, it looks like what you posted is output, but I don't know what your data looks like.

Jonathan

• ###### 14. Re: How do I sum only postive numbers in a given column of data?

Hi Jonathan,

I have attached additional data in an excel file with an accompanying pivot table that illustrates the calculation I am attempting to  create in Tableau. In the file we have 5 customers and 8 accounts and 44 orders and the accompanying value (positive and negative) What I am trying to do is sum orders by account by customer.  I hope this helps.

In summary, I would like Tableau to generate a report that shows the results of the pivot table with the addition of the sumif formula. I have created a sample report in the file as well

Thanks!

1 2 3 Previous Next