Tableau Volume calculation based on Start Date and End Date

0
votes

Using Tableau, I need to create a visualisation that shows me the total volume of started and ended work items per day.

Typical sql data consists of columns for each:

Reference (Unique Work Item Reference) Start Date (Date Work Item Started) End Date (Date Work Item Finished)

For example, if on the 6th of April 2018, 55 work items were started and 5 items were finished. Each represented date should show in its own line over time.

The issue I'm getting at the moment, when I have start date as a continuous column, it calculates the number of work items started for the end date and not the number of items finished for the end date.

Any help guidance would be greatly appreciated.

Tableau Public Link

4
tableau-apivolumecalculated-field

4 Answers

1
votes

This is a simple problem if you correct the shape of your data. You can achieve this by using the pivot functionality in Tableau or Custom SQL(If your data source does not support pivot).

  1. Pivot the date columns

enter image description here 2. Rename the pivoted fields

enter image description here

  1. Now build the view as below.(null values are due to tasks that are not yet closed, you can just filter it out)

enter image description here

0
votes

I assume that the data is of this form Data structure With this format, you could achieve something like this Gantt Chart If you notice, I have created a calculated field called "Duration" which is in size shelf. the calculated field reads

If [Status]="Finished" then
[End Date]-[Start Date] ELSE
TODAY()-[Start Date]
end

Hope this helps! Let me know if this is not clear enough

0
votes

Even if the Status column is not there in the data source, it can be added using the following code

if ISNULL([End Date]) then 
"WIP" else "Finished" end

Also, is this how you want to see the information? Click here