4 Replies Latest reply on Sep 27, 2016 11:09 PM by Chris Soriano

    Total from Blended Data Sources

    Chris Soriano

      I need to get a count of all activities logged by our users. However, these activities are logged in 4 different data sources: Appointments, Emails, Phone calls, and Tasks. They all have one thing in common, user id. I was able to create a relationship between all of them and used Emails as the primary source. However, when I tried to create a formula field to get a running total of all activities by user, I only received totals for users who logged a record against each of the four activity type.

       

       

      Here's my formula for # of activities:

       

      SUM([# of Emails])+sum([TS5001_CData_MSD_Appointments].[# of Appts])+sum([TS5001_CData_MSD_Phone Calls].[# of Calls])+sum([TS5001_CData_MSD_Tasks].[# of Tasks])

       

      How do I get Tableau to calculate the total for all columns irrespective of type of activity entered?

        • 1. Re: Total from Blended Data Sources
          Allan Marcus

          I don't think you are using the term "running total" correctly. A running total is like a balance for a checkbook.

           

          Have you tried just using the Analysis menu, totals, Show Grand Totals?

          • 2. Re: Total from Blended Data Sources
            Chris Soriano

            Yes, it only shows the total for each column, but not for each row.

             

            I actually just want to show the Total. I've added the other column fields, Appts, Emails, Phone Calls, and Tasks just to QA.

            • 3. Re: Total from Blended Data Sources
              Chris Soriano

              I was able to get help and resolve this issue:

               

              Tableau skips records that have a NULL value. I needed to create a calculated field that tells Tableau not to skip a record because it contains NULL values. To do that, I created a calculated field for each activity type:

               

              # of Calls

              IFNULL(SUM([TS5001_CData_MSD_Phone Calls].[# of Calls]),0)

               

              # of Emails

              IFNULL(sum([Number of Records]),0)

               

              # of Appts

              IFNULL((SUM([TS5001_CData_MSD_Appointments].[# of Appts])),0)

               

              # of Tasks

              IFNULL((SUM([TS5001_CData_MSD_Tasks].[# of Tasks])),0)

               

              # of Activities

              [# of Emails] + [# of Appts] + [# of Calls] + [# of Tasks]

              • 4. Re: Total from Blended Data Sources
                sivasangari Chinnappa

                Hi Chris,

                 

                When there is no data in particular type of activity, it will be treated as null. Hence when we add null with a numeric data it gives no result. Try to use zn function so as to convert all null to zeros.

                 

                zn(sum([emails) + zn(sum([[TS5001_CData_MSD_Appointments].[# of Appts]) + zn(sum([TS5001_CData_MSD_Phone Calls].[# of Calls])) + Zn(sum([TS5001_CData_MSD_Tasks].[# of Tasks])).

                 

                Please find attached the workbook for better explanation.

                1 of 1 people found this helpful