Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Calculating measures from time dimension
Posted June 21st, 2008 by oruvan
I have a source excel file with the following columns, and have included sample data...
Source Date
-----------------
100*4/1/2008*5/1/2008**
101*4/2/2008*4/3/2008**
103*5/1/2008*5/3/2008**
104*5/2/2008*6/1/2008**
Desired Output in Tableau
--------------------------------
<#ofCasesOpen><#ofCasesClosed>
Apr2008*2*1
May2008*2*2
June2008*0*1
How do I acheive the above in tableau ? Looks simple, I have been trying to break my head, no luck thus far...
Subscribe to the comments on "Calculating measures from time dimension"
Comments
I'll assume the format of your input data is:
Case#*OpenDate*CloseDate
If so, this is a little tricky. The problem is that you want case 100, which is all in one row, to contribute it's contents to two different output rows. If you can, i'd recommend a table that looks like
Case#*Date*Action
100*4/1/2008*Open
100*5/1/2008*Close
However, you may not have control of your data input format. To do this with the input data you have, you really need to union the table with itself so that every row can be counted twice.
You can do this in tableau. When you create your connection, after you choose the table, instead of clicking okay, click the 'Custom SQL' button. This will copy the SQL to select from the selected table into a text window. It may look like this
SELECT [Sheet 1$].[CaseNum] AS [CaseNum],
[Sheet 1$].[OpenDate] AS [OpenDate],
[Sheet 1$].[CloseDate] AS [CloseDate]
FROM [Sheet 1$]
for example. To create the format i'm suggesting you'll want to transform it in several steps. First, copy the entire text and paste it on the end, then between the two copies, type the words 'UNION ALL'.
SELECT [Sheet 1$].[CaseNum] AS [CaseNum],
[Sheet 1$].[OpenDate] AS [OpenDate],
[Sheet 1$].[CloseDate] AS [CloseDate]
FROM [Sheet 1$]
UNION ALL
SELECT [Sheet 1$].[CaseNum] AS [CaseNum],
[Sheet 1$].[OpenDate] AS [OpenDate],
[Sheet 1$].[CloseDate] AS [CloseDate]
FROM [Sheet 1$]
Now, change the first copy to be the 'open' information by transforming like this:
SELECT [Sheet 1$].[CaseNum] AS [CaseNum],
[Sheet 1$].[OpenDate] AS [Date],
'Open' AS [Action]
notice that the CloseDate was replaced by the 'Open' AS [Action] line and the output name of [OpenDate] was changed to [Date]
Now, make a similar change to the second half, making it the 'close' information:
SELECT [Sheet 1$].[CaseNum] AS [CaseNum],
[Sheet 1$].[CloseDate] AS [Date],
'Close' AS [Action]
The final SQL should look like this:
SELECT [Sheet 1$].[CaseNum] AS [CaseNum],
[Sheet 1$].[OpenDate] AS [Date],
'Open' AS [Action]
UNION ALL
SELECT [Sheet 1$].[CaseNum] AS [CaseNum],
[Sheet 1$].[CloseDate] AS [Date],
'Close' AS [Action]
When you connect to this, you should be able to do what you want.
Good luck.
:)ross
Ross, thanks for the information and sorry about the delay.
The information you provided was very useful and highlights the power of tableau.
However I get the idea on how to do this excel, my source data is in a oracle table. Any ideas how do I implement the above logic for oracle ?
Thanks in advance
-ansa
That should be very similar. Where you use [Sheet 1$] for Excel, you'd put in your table name for Oracle. Any particular difficulty? (I haven't tried this.)