CNTD and Running Totals: A word of caution

I have a business operation in which people call my call centre. The same person can call the call centre more than once, but we can identify the caller from their details using a field called Caller ID.

I need to know how many unique people called the call centre each month (based on Call Date And Time), but I wanted the cumulative number at the end. I.e. What is the total number of people that have called my call centre.

I tried to do this using probably the most obvious way:

  • Drag Call Date And Time to the columns, and select Month. It appears as a blue pill
  • Right-click and drag Caller ID to the rows and select CNTD (count distinct). It appears as a green pill
  • Apply a quick table calculation to the Caller ID pill, making this a Running Sum.

The line goes up nicely, and if I use the same running sum as a label, I can see that the total number of callers to the call centre at the end of the last month is 34,567.

Except that it’s not. It’s somewhat lower than that.

You see, the 34,567 that appears against the last month is the total number of unique callers in month 1, plus the total number of unique callers in month 2, plus … plus the total number of unique callers in the final month.

If Jane Bloggs called up in month 1 and again in month 2, she contributes twice to the 34,567 total. In other words, Tableau sums the distinct counts for each month rather than providing a total unique count to date.

To get around this, we need to create an LOD calculation to create a new field called First Call:

IF {FIXED [Caller Id] : MIN([Call Date And Time])} = [Call Date And Time] THEN 1
ELSE 0
END

This basically marks the first call for each Caller ID with a 1.

Now, instead of using the CNTD(Caller ID) in our rows, we can use SUM(First Call). Again we can apply a table calculation to give ourselves the Running Sum.

This time, the total will be lower than the 34,567, and will reflect the true number of unique callers. And each month’s total will only count the *new* folks that called during that month.

One thought on “CNTD and Running Totals: A word of caution

Leave a Reply

Your email address will not be published. Required fields are marked *