4 Replies Latest reply on May 31, 2016 10:54 PM by Alex Ixeras

    Counting Unique Grand Total

    Alex Ixeras

      Hi,

       

      I have a table with Customer IDs and interactions of that customer on a daily basis. I want to be able to count the number of interactions in a given time frame and show how many counts of those interactions I've had. The attached screenshots might explain that in better detail than I can describe here.

      Tableau - Interactions - Concept.png

      I have set up Tableau the following way, representing step 1:

      Tableau - Interactions Grand Total.png

       

      How do I get to count the unique grand totals for step 2?

       

      I have:

      • Analysis > Aggregate Measures : On
      • Analysis > Totals > Show row grand totals : On
      • Analysis > Totals > Show column grand totals : On

       

      And this is what I have for the count mark:

      Count Mark.png

       

      Thanks!

        • 1. Re: Counting Unique Grand Total
          Andrew Watson

          Look at LOD calculations, they might do it for you. {FIXED [CustomerId]: COUNT([Interaction Code])}

           

          That formula should be usable to create 2 and 3. The date field would need to be a context filter.

           

          This link will help: Should I use FIXED or INCLUDE/EXCLUDE?

          • 2. Re: Counting Unique Grand Total
            Alex Ixeras

            Thanks, Andrew. Two further questions:

            1. I guess I need to put the LOD calculation in the rows. But what do I put in the columns?
              If I do that and leave the Date in the columns, I get the following at the moment. No numbers.


            2. I named the LOD calculation "Calculate Unique Interactions". Should I then select Sum of "Calculate Unique Interactions" or should it be Count (Distinct)?

             

            The table behind looks like this:

            Interaction Date   Customer Id   Interaction Code

            01/04/2015         1000705       DI_M

            01/04/2015         1000713       DI_M

            05/04/2015         1000705       DI_M

            11/04/2015         1082489       DI_M

            20/04/2015         1002486       DI_M

            21/04/2015         1188631       DI_M

             

            Interaction Code 'DI_M' is what I filter on. E.g. Customer Id 1000705 appears twice, so I'd want that to be counted as 2 if I filter for the date range that includes the two dates 01/04/2015 and 05/04/2015.

             

             

            Thanks!

            • 3. Re: Counting Unique Grand Total
              Andrew Watson

              Based on your provided dataset and creating the chart labeled 3 at the very beginning of this thread I get the following:

               

               

              Calculation 1 is the formula: {FIXED [Customer Id]: COUNT([Interaction Code])}

               

              Adding the filter gives the following:

               

               

              Is that what you are looking for?

              • 4. Re: Counting Unique Grand Total
                Alex Ixeras

                Thanks, that's it. I think what I did wrong was that my Interaction Date and Customer Id were a Dimension instead of a Measure and that my LOD was a Measure instead of Dimension. That's how they were in the dataset that I'm working on. When do I need to set which data to Dimension or a Measure?