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.

Creating aliases without breaking your blends

Aliases are lovely. They allow you to see what I’ve always termed “friendly names” for field values as opposed to codes and the like. You may have legal entities that are referenced as numbers in the underlying data but you want to display as the names of those entities to the users. You may have state abbreviations that you want to display in full, or a handful of currency codes that you want to show as symbols or full currency names.

I learnt the hard way that creating aliases can be dangerous. Originally, I had no idea that when blending, Tableau uses the alias values as opposed to the raw values coming in on the data feeds. And I learnt that the hard way.

Let’s say my primary data source contains currency codes (EUR, GBP, USD etc.) and this is blended to a secondary data source that contains FX rates that also use those codes. If I add aliases ($, £, €) to the currency codes in the primary data source, then my blend breaks. (I could fix it by applying the same aliases to the secondary data source. But this is not always efficient or wanted.)

To me, this is undesired behaviour. I see aliases as being purely presentational. If I am using a field in two data sources to blend, I want Tableau to respect the raw value rather than some name I’ve chosen to to display to the user.

There is, however, a simple way round the problem: Duplicate your field.

Use the duplicate field to blend on, and apply your aliases to the original field. That way, the integrity of the original field’s name remains intact, and its aliases can be used in presenting the information to the user.

Aside

Polishing your dashboard: Tidy your tooltips and axis titles

If you want to polish your dashboard, make sure your tooltips and axis titles are tidy.

Tableau will always create default values for these, but these are often ugly, particularly if you’ve got table calculations going on.

Before you publish your dashboard, go into each sheet and check the titles of your axes. And make sure the structure of your tooltips makes sense from a user perspective.

I find that these two pieces are often overlooked. I’ve been known to overlook them myself.  Getting them right will likely pass your users by. But getting them wrong will ensure they don’t forget, for all the wrong reasons.

You can change the name of your “Number of Records” field

So in Tableau, whenever you create a new data source, it comes with an additional field that wasn’t in the data itself. This field is called Number of Records and appears in italics at the bottom of your measures.

If you right-click and Edit the field, it simply has a formula of 1. This means that every record takes the value 1, and so when you use this in dashboards, which you no doubt will, it acts as a record counter. When you do, any axis, tooltip etc. will get the name Number of Records.

But I discovered by accident and with utter delight that the name of this field is editable. So you can change the field to read Number of Accidents, Number of DealsNumber of Invoices, whatever your data represents. And this is delightful, as all of your axes and tooltips will have their labels changed.

Why it took me so long to discover this, I’ll never know. But now that I know it, it’s usually one of the first things I do.