CODEWITHSUNDEEP
tableau-api
A. McMaster
A. McMaster

Reputation: 303

Tableau - Finding Records that happened within X amount of time

I have some experience with Tableau as I have migrated into a Data Analyst position with not a ton of experience and am hitting a wall with if it is even possible to perform the calculation within Tableau.

The general base of my data would look like (Limiting it to the two fields we need)

  1. Case # | Tech Dispatch Time (PST) | Full Time (Y/N)
  2. 2017-0001| MM/DD/YYYY 16:34:21 | 0
  3. 2017-0002| MM/DD/YYYY 20:43:00 | 1
  4. 2017-0003| MM/DD/YYYY 22:00:05 | 0

We are looking to see how often a dispatch happens within X amount of time to determine our efficiency in dispatching our teams. If these were happening on the same record, a basic DATEDIFF and IF function would work, but I am at a loss on how to state that IF there is another dispatch in say 180 minutes, 1, else 0.

Is this something I will have to do outside of Tableau within say SQL or PowerPivot? Or is this a function that Tableau could calculate as well?

Thank you for your time and assistance with this matter,

Andy M.

Upvotes: 1

Views: 79

Answers (2)

Sam M
Sam M

Reputation: 4166

Use the LOOKUP() function. It allows you to find other rows' values relative to the current row.

DateDiff("minute", [Tech Dispatch Time (PST)], Lookup(MIN([Tech Dispatch Time (PST)]), -1))

The sample data you have in your question presents a problem though. The Tech Dispatch Time (PST) field only has the time portion. To accurately calculate time difference, you need a date with the time. Otherwise, you will end up with negative times if the two cases span across midnight.

Since this is a table calc, you'll also have to set the partitioning and addressing dimensions and sort order by editing the table calc to get the effect you want.

Upvotes: 1

Jose Cherian
Jose Cherian

Reputation: 7737

Create a parameter to define your 'x', Then create a calculated field with below Formula(I called it time block)

//We need to add 0.5 to Roundup
ROUND((DATEDIFF('minute',[Tech Dispatch Time],NOW())/[x])+0.5,0)

Now you can use it in your viz enter image description here enter image description here

Upvotes: 2

Related Questions