KNOWLEDGE BASE

How to Create Day over Day Count of Closed Cases


Published: 30 Oct 2017
Last Modified Date: 31 Oct 2017

Question

How to create day over day count of closed cases.

Environment

  • Tableau Desktop
  • Windows 7
  • Excel/SalesForce

Answer

See the attached workbook as an example.

The first two sheets show two different ways to create the desired count of closes. 

Step 1

We can either simply count the boolean field (if contained in the data source):

COUNT(Closed) 

     or 

COUNT(if not ISNULL([Date/Time Closed])
THEN [Date/Time Closed]
END) 

These two options result in the exact same value. See Sheets "Day of Date/Time Closed (Simple)" and "Day of Date/Time Closed" for comparison. There are actually a few more ways to calculate this number, but either of the above two should suffice. 

Step 2

We can then place DAY(Date/Time Closed) and the COUNT of closes on columns and rows respectively and add COUNT of closes to the Text Marks card. This will create a view showing how many cases were marked as closed truncated to DAY. See Sheet "Day of Date/Time Closed".

Step 3

Now, we can write a simple calculation, in this case called "difference from previous day", to calculate the difference in cases closed from the previous day:

LOOKUP([count of closed (simple)],0) - LOOKUP([count of closed (simple)],-1)

The "difference from previous day" calculation can be dragged onto the Text Marks card.

Step 4

We then created a calculation called "Growth":
 
IF 
[difference from previous day] > 0 THEN 'More Than Previous Day' 
ELSEIF
[difference from previous day] = 0 then 'No Change' 
ELSEIF
[difference from previous day] < 0 then 'Less Than Previous Day' 

END

The "Growth" calculation can now be dragged onto both the Color and Size Marks cards. Now we observe red lines indicating Cases closed are decrease from the previous day, and blue/purple lines indicating Cases closed are increasing compared to the previous day.  See the "Solution" Sheet.

Additional Information

The referenced fields are generally available when using SalesForce as a data source.
Did this article resolve the issue?