9 Replies Latest reply on Sep 6, 2018 5:51 PM by Okechukwu Ossai

# Calculation is doubling \$ amounts

I have a data set that includes giving credit to multiple fundraisers.  The credit field is popluated as such:

• Swift
• Dwyer
• Johnston
• Dwyer; Johnston
• Johnston; Swift
• Swift; Dwyer
• Dwyer; Swift

I created a calculated field so that I could list their total dollars raised individually, even if they have shared credit

IF CONTAINS([CFR Member Credit],"Swift") THEN "Swift"

ELSEIF CONTAINS([CFR Member Credit],"Dwyer") THEN "Dwyer"

ELSEIF CONTAINS([CFR Member Credit],"Johnston") THEN "Johnston"

END

However, the sum totals when applying this it is not adding up correctly. It appears that it's duplicating some gifts, but it appears random. I'm thinking I need a more complex calculation, but I can't seem to find which gifts are duplicating.

Any thoughts on what I need to look at?  Thanks!

• ###### 1. Re: Calculation is doubling \$ amounts

Good morning

I would have to see the data but the conditional IF statement is processing each record individually - once a record returns a True the Then clause is executed and the next record is processed.  Double \$ amounts would result from duplicate records in the data set - suggest you look at the records to see if they were duplicated

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calculation is doubling \$ amounts

Hi Heidi,

To be able to answer the question of how much was raised per creditor, you will have to restructure the data.

As Jim mentioned above a calculated field will only create one flag per row. So if you have "Johnston; Swift" this will only capture Johnston/you will loose Swift - that is if Johnston appeared first in the calculation.

In any case, you need to split the creditor column so that you have a single name per row and make a decision of whether you will split the dollars or duplicate.

Before: Johnston; Swift  50

After: Johnston 25

Swift       25

Let me know if this helps or if you need further assistance.

Cheers,

Sasha

• ###### 3. Re: Calculation is doubling \$ amounts

There are no duplicate rows in the data as we are connected to our database in realtime.  I can't share the data completely, but here is a small set of what my fields look like.  Each row is a single gift and 100% credit is shared and counted toward their overall individual goals.

 CFR Member Credit Fiscal Year Gift Amount Swift FY18 \$25,000.00 Swift; Dwyer FY17 \$75,000.00 Swift; Johnston FY17 \$15,000.00 Dwyer FY14 \$25,000.00 Dwyer; Swift FY15 \$30,000.00 Johnston FY17 \$25,000.00

To be fair, I am fairly new to Tableau, so I've been researching solutions online.

• ###### 4. Re: Calculation is doubling \$ amounts

so where do you believe records 2 and 3 go ?  all 3 will go to "Swift"

Recorde 4 and 5 will go to Dwyer

and 6 will go to Johnson

the record is processed once - once there is a match on the Contains clause the Then clause is processed - then the next record is processed

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 5. Re: Calculation is doubling \$ amounts

It should come out like beow, where each fiscal year would total for each individual, no matter where there were in the list.

So looking FY17, it would sum up all the gifts for Swift (#2 and #3); Johnston (#3 and #6); and Dwyer (#2)

 FY14 FY15 FY16 FY17 FY18 Swift \$30,000 \$90,000 \$25,000 Dwyer \$25,000 \$30,000 \$75,000 Johnston \$40,000
• ###### 6. Re: Calculation is doubling \$ amounts

If that is your expectation you will need to restructure your data to

separate out the records so only one donor is identified with each record.

Jim

On Fri, Aug 24, 2018, 5:19 PM Heidi Saperstein <tableaucommunity@tableau.com>

• ###### 7. Re: Calculation is doubling \$ amounts

As mentioned by Jim and Sasha, your data needs restructuring. However, an alternative approach will be to use a cross join.

Cross joins tend to increase the size of your data, so the effectiveness of this solution will depend on how big your data is. You also need Tableau Version 10.2 and above. See attached workbook and Excel datasource

Step 1: Create a list of all the members in your dataset. I've done that in Excel, a worksheet called 'Members'. Then inner join this to the gift table using a join calculation 1 = 1.

Step 2: Create calculated field [Member]. Add it to the filter shelf and exclude null.

Hope this helps.

Ossai

• ###### 8. Re: Calculation is doubling \$ amounts

This might work!  Can I connect this with live data?  I can't seem to connect a list of team members with my live data set.  Also, I would only want to use this "joined" data on one worksheet, not all of them.

• ###### 9. Re: Calculation is doubling \$ amounts

You can create 2 datasources; a cross join datasource for the single worksheet and then a separate data source for other worksheets. What kind of database do you have? Not all databases support cross join. See link below.