CODEWITHSUNDEEP
dashboardtableau-desktop
Dhruv
Dhruv

Reputation: 43

Calculate total time based on column in Tableau

I have a table like below:

From Date Issue Id Issue Id (group) Status Till Date
19-07-2021 17:21 4 4 Approved 19-07-2021 17:23
19-07-2021 17:23 4 4 In Progress 19-07-2021 17:23
19-07-2021 17:23 4 4 In Review 19-07-2021 17:25
19-07-2021 17:25 4 4 In Progress 19-07-2021 18:56
19-07-2021 18:56 4 4 In Review 20-07-2021 08:47
20-07-2021 08:47 4 4 Resolved 20-07-2021 14:45
20-07-2021 14:45 4 4 Closed
12-07-2021 10:49 4 4 Open 19-07-2021 17:21
27-04-2016 09:07 3 3 Open 10-01-2017 08:40
10-01-2017 08:40 3 3 Closed
10-01-2017 08:40 3 3 Resolved 10-01-2017 08:40

I need to do the following things:

  1. For Issue Id 4 find the total time in hours or minutes or seconds or days for a particular type of status. For e.g There are 2 In Review rows. So the total time between From Date to Till date will be 17:23 (19-07) till 8:47(20-07).
  2. calculate total time a issue is in between closed and In Review (here Till date for closed issues is unfortunately null).

Basically I am trying to create a dashboard where for each issue i'd i would like to see for how long was a issue "In Review" or "In Progress" before it was closed. So the dashboard will have "Issue Id" in the X axis and "Total Time for Review" or "Total Time for Progress" in the Y axis. For e.g Issue 4 was in a total of 1:31:01 Hours in the "In Progress" state (17:23 to 17:23 on 19th July and 17:25 to 18:56 on 19th July).

I am trying this: IF [STATUS] = 'In progress' and [STATUS] = 'Closed' THEN DATEDIFF('day',[Date Create],[Till Date]) END but it says tables can only be aggregated and using Count function only.

Can someone please help? How can we create a calculated field for the above scenarios.

Upvotes: 0

Views: 123

Answers (1)

Matthew Walkup
Matthew Walkup

Reputation: 76

Think of your IF statements being applied to each row, you cannot have a status that is both in progress and closed.

I would arrange the text table like this:

Columns: Status
Rows: Issue ID (group) | Issue ID 
Text Mark: Calculated Field (Named something like Total Time).

That will group all of the statuses together. You can change the aliases of the status if you want to say "Total Time for ..."

Then your calculated field would be:

DATEDIFF("day", [From Date], [Till Date])

And make sure you drag the pill over it is summing it. That will collapse everything at the status level, and then total the days.

Upvotes: 1

Related Questions