2 of 2 people found this helpful
Tableau can do both of the calculations you are seeking to do here. But I'll warn you up front that the second one is a bit more of a challenge. The workbook attached shows a Sheet 1 and a Sheet 2 with each sheet matching the value provided above.
The first example is straight forward with Taleau and this data. Place Date on the Columns shelf, set it to Exact Date from the drop down menu that appears on the field and then go back to the menu and set it to utilize a discrete value. Next, put the Cust # on the Rows shelf (this should be a Dimension in Tableau and if it is not, just drag the field from Measures to Dimensions before you use it). Finally, drag Status to the Text Mark on the Marks shelf.
Sheet 2 is harder and requires (at least the only way I can do this) utilizing a new connection to the data source and a Custom SQL query. We need a new field to be generated based upon the value of the previous day and current day status fields. To do this, Tableau has a great feature that allows a custom SQL connection to the data source. If the file, like mine, is Microsoft Excel, then you will need to utilize the "Legacy Connector" option when setting up the data source. Do this by selecting the option when connecting to the file. Instead of clicking "Open", Select the down arrow next to the word "Open" and select "Legacy Connector". Now you will be able to edit the original connection with the option "Convert to Custom SQL".
Here is the custom SQL that I used to make the data table with a new field needed:
SELECT [Sheet1$].[Cust #] AS [Cust #],
[Sheet1$].[Date] AS [Date],
[Sheet1$].[status] AS [status],
[Sheet1$1].[Cust #] AS [Cust # (Sheet1$1)],
[Sheet1$1].[Date] AS [Date (Sheet1$1)],
[Sheet1$1].[status] AS [status (Sheet1$1)],
[Sheet1$].[status]+[Sheet1$1].[status] AS [Previous and Current Status]
INNER JOIN [Sheet1$] [Sheet1$1] ON ([Sheet1$].[Cust #] = [Sheet1$1].[Cust #]) AND ([Sheet1$].[Date] = [Sheet1$1].[Date]-1)
The key to this is that inner join on the current date and previous date fields. With this, we can create a combined status field - this is defined as [Previous and Current Status].
oh - one final thing I did - the Count could just be done by dragging Number of Records to the view. I modified it slightly to fill the null values with zeros as in your example.
I do that here:
ifnull(Lookup(sum([Number of Records]),0),0)
Check out the attached workbook. If you have familiarity with a SQL, this should be pretty straight forward. The workbook is in Version 10 of Tableau.
I hope this helps.
Great thanks for your help!