1 2 Previous Next 17 Replies Latest reply on Aug 13, 2018 7:51 AM by Kelly McGrady

# Sum and If calculation

Despite my best efforts, what should be a simple summation is stumping me. Here is my criteria in Tableau:

sum(if [Close Date]>=#2018-01-01#

and [Donation Record Type]='012o0000000Ik8TAAS'

and [Stage] <> 'Closed Lost'

and [Stage] <> 'Closed Won'

and [Stage] <> 'Withdrawn'

and [Amount] < 10000

Then 1

Else 0

END)

Here is, what should be, the exact same criteria in SF:

 Close Date greater or equal 1/1/2018 AND Donation Record Type equals Cultivated Gift AND Stage not equal to closed lost AND Stage not equal to closed won AND Stage equals withdrawn AND Amount less than "10,000"

However, in Tableau it's calculating over 65,000 records but SF is showing 21 records. SF is right, Tableau is not, and I can't figure out where my error is in Tableau.

• ###### 1. Re: Sum and If calculation

Could you please attach the sample workbook to see the granularity further to know why 65000 coming

BR,

NB

• ###### 2. Re: Sum and If calculation

Hi Naveen,

I would love to but my data set is pretty large so a packaged workbook is well over 100mb. If there is a way to save a packaged workbook with only a small part of the data, or if there something else I could screenshot to help show what I'm doing wrong I'd be happy to do so.

• ###### 3. Re: Sum and If calculation

Tableau: <>

SF: =

• ###### 4. Re: Sum and If calculation

Have you tried or instead of and?

• ###### 5. Re: Sum and If calculation

Nice one didn't catch the eye as usual you are awesome

@Adam - Kindly check this out

BR,

NB

• ###### 6. Re: Sum and If calculation

No problem I hope it helps.

• ###### 7. Re: Sum and If calculation

You know what?  We're ALL wrong.  The only "stage" test he needs is [Stage]='withdrawn'.

The other 2 are superfluous: if [Stage] is equal to 'withdrawn', by definition it CAN'T be equal to anything else.

• ###### 8. Re: Sum and If calculation

Ack, that's not the underlying problem (SF returns 496 with the corrected criteria) but still made me look like a fool

All three stages should be <>

• ###### 9. Re: Sum and If calculation

So do you need the ones where stage is null?  ISNULL([Stage])

• ###### 10. Re: Sum and If calculation

sum(if [Close Date]>=#2018-01-01#

and [Donation Record Type]='012o0000000Ik8TAAS'

and ([Stage] <> 'Closed Lost' OR [Stage] <> 'Closed Won' OR [Stage] <> 'Withdrawn')

and [Amount] < 10000

Then 1

Else 0

END)

• ###### 11. Re: Sum and If calculation

Deepak,

and ([Stage] <> 'Closed Lost' OR [Stage] <> 'Closed Won' OR [Stage] <> 'Withdrawn')

This can not be correct. This condition will not do anything basically. Suppose stage = 'Closed Lost'  and then since you have put OR with next condition ([Stage] <> 'Closed Won' ), 'Closed Lost'  will qualify, like wise for others and any value outside of these 3 will anyway qualify.

Thanks,

Ankit Bansal

• ###### 12. Re: Sum and If calculation

Ok, so I decided to write and test the formula line by line to see where the error is:

sum(if [Close Date]>=#2018-01-01#

Then 1

Else 0

END)

Result: 38,012,776

• ###### 13. Re: Sum and If calculation

You may have to turn your date into a string and I'm not quite sure but I'm fairly sure that using " "  would be proper for the comparative date string "2018-01-01".

• ###### 14. Re: Sum and If calculation

WHAT IN THE WORLD IS HAPPENING

Ok, sorry. There are currently 1.4 million donation records in my SF database.

count([Donation ID])

returns 114 million records. Donation ID's are obviously unique identifiers. So, I think the logic in the formula is OK. Does anyone see anything in the data connection that would cause Tableau to count each donation record 100 times?

1 2 Previous Next