8 Replies Latest reply on Oct 8, 2018 5:38 PM by Ken Flerlage

# How to plot running count of a dimension?

I am creating a dashboard of our door data in Tableau. New doors are added to the database every day. Each door ID is associated with a insert timestamp. Now I want to plot the growth of total door count. The idea is to count the distinct "Active Door ID" with "ETL Insert TS".

I first created a calculated field called "Door COunt by Date" which is defined as "{ FIXED [Door Insert Date]: countd([Active Door Id])}". Then I created "Running Door Count": "RUNNING_SUM(SUM([Door Count by Date]))".

When I plotted the "Running Door COunt" by the week of "Door Insert Date". The trend seems ok which increases gradually. However the total count at the end is way bigger than the actual door count (Countd(active Door Id)). I think the problem is some doors are counted more than once. But I don't know how to correctly plot this with the actual numbers.

• ###### 1. Re: How to plot running count of a dimension by month?

You need to extract your data and save the workbook, otherwise we can't do anything.

• ###### 2. Re: How to plot running count of a dimension by month?

Sorry. Updated

• ###### 3. Re: How to plot running count of a dimension?

I don't really see any issue here. If I count the distinct doors, I get 9026, which is pretty much the same number as you had.

That being said, I do think these calculated fields may be over-complicating things. Instead of those, you could just drag Active Door ID to the rows shelf, change it to a Count Distinct Measure, then right click, choose "Quick Table Calculation" then "Running Sum"

• ###### 4. Re: How to plot running count of a dimension?

You are right about the calculated field. Just do the running total on "Active Door ID" does just that. And the total is indeed correct.

However, when I tried to calculate the running sum of the other dimension "Email Addr" in order to count the total of technician by date. The number is much higher than countd(email addr). The running total of distinct "Email Addr" is close to 2400 to date. But the actual count of distinct email addr is just 524. I have no idea where this discrepancy comes from. Workbook attached.

• ###### 5. Re: How to plot running count of a dimension?

Hi dian

In your cound(Email Addr) coming around 2325. Please go to grandtotal and select sum and get correct value.

First countd selected with sum and second one not selected with sum.

Thanks

sankar

• ###### 6. Re: How to plot running count of a dimension?

Hi Sankar,

Actually the 524 is the correct number at the end of plot. If I do "countd(email addr") without any time constraint, that's exactly what the total distinct count of email addr.  I want to show the growth of "email addr" so I am not adding number of "email addr" for every month. How is this 524 number calculated in your example? How do I plot the growth of "Email addr". Doing running sum always ends up with 2325 at the end of the plot.

• ###### 7. Re: How to plot running count of a dimension?

You should join your data with another file/table that can give you a list of all the month numbers. You can then achieve your desired result.

To show how it works, here is what I did:

1. Copied the email address and ETL Insert TS into an Excel file; and created another sheet called Month Numbers with the values as shown below:

2. Joined the above data in Tableau as shown below:

3. Created a calculated field to trim the email addresses and have them all in lower case, and then did a distinct count on them. No, table calculation is required here.