KNOWLEDGE BASE

## How to Create Day over Day Count of Closed Cases

Published: 30 Oct 2017

### Question

How to create day over day count of closed cases.

### Environment

• Tableau Desktop
• Windows 7
• Excel/SalesForce

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.