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

    Calculation is doubling $ amounts

    Heidi Saperstein

      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
          Jim Dehner

          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
            Sasha Hanna

            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
              Heidi Saperstein

              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 CreditFiscal YearGift Amount
              SwiftFY18$25,000.00
              Swift; DwyerFY17$75,000.00
              Swift; JohnstonFY17$15,000.00
              DwyerFY14$25,000.00
              Dwyer; SwiftFY15$30,000.00
              JohnstonFY17$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
                Jim Dehner

                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
                  Heidi Saperstein

                  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)

                   

                      

                  FY14FY15FY16FY17FY18
                  Swift$30,000 $90,000$25,000
                  Dwyer$25,000$30,000 $75,000
                  Johnston $40,000
                  • 6. Re: Calculation is doubling $ amounts
                    Jim Dehner

                    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
                      Okechukwu Ossai

                      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
                        Heidi Saperstein

                        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
                          Okechukwu Ossai

                          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.

                           

                          Integrate your data with cross-database joins in Tableau 10 | Tableau Software