You need to use a Union query here. The first half of the Union can be to get you the Opened information, while the second half can be the Closed information. This will ensure that you have all the dates and nothing is really missed out.
I think you may have missunderstood. My issue is not with the query. The SQL query being used to give me task data already contains all the information I need. Each task is a row and there are several date columns for when the task was opened, resolved, closed, etc.
My issue is in the report's design interface - when I place either the opened or the closed datetime as a colum pill Tableau is automatically going to filter out everything else. That's the problem.
For example, if I pill opened as a column then each column would contain only those that are opened in that period. To calculate closed/opened for each month I would need to include all tasks.
1 of 1 people found this helpful
Let's assume your data is structured like this:
ID Open Date Resolved Date Close Date 1 1/1/18 1/10/18 2/12/18 2 1/15/18 1/20/18 3 2/1/18 3/2/18 4/15/18 4 2/15/18 3/10/18 5/20/18 5 3/1/18
Now, if you use Open Date, you should have dates from January to March. But, if you use Resolved Date, you should have January and March only. And if you use Close Date, you should have February, April and May. But in reality, we should see all the 5 months, January to May irrespective of whether you are using Open, Resolved or Close dates. Do you agree?
If yes, then you need to pivot your data as shown below:
ID Date Date Type 1 1/1/18 Open Date 2 1/15/18 Open Date 3 2/1/18 Open Date 4 2/15/18 Open Date 5 3/1/18 Open Date 1 1/10/18 Resolved Date 2 1/20/18 Resolved Date 3 3/2/18 Resolved Date 4 3/10/18 Resolved Date 1 2/12/18 Close Date 3 4/15/18 Close Date 4 5/20/18 Close Date
Now, you have only 1 column for Date to use and you should see all the 5 months. You can then use the Date Type to count how many tickets have been opened, resolved or closed.
If you are convinced with the above solution, you need to do the similar thing with your data from SQL Server. And that's why I suggested that you do a Union. Assuming you have the same 3 date types as above, your Union should contain section for each of them.
Thanks for explaining that a little better. Sadly, with my data containing millions on rows and spanning several datetime columns I don't know how feasible that kind of join is going to be - plus it would make my extract gigantic. I'll see what I can do about creating a second dataset that only contains the PKID, the date and the action.
Well, to reduce the volume of data, you need to aggregate, say at a monthly level, and then proceed in the suggest way.