4 Replies Latest reply on Sep 15, 2017 10:33 AM by sterling.ku

# Calculating date differences between multiple records

Hi all,

I am having trouble on calculating the most recent date when given multiple records and the difference between dates when keeping a field consistent.

Here is sample data that I would work on:

 Audit Date Current Status Incident ID Previous Status 8/10/17 12:00 AM Assigned 111 (null) 8/10/17 2:00 PM Assigned 111 Assigned 8/10/17 5:00 PM Assigned 111 Assigned 8/12/17 12:00 AM In Progress 111 Assigned 8/13/17 12:00 AM Pending 111 In Progress 8/14/17 12:00 AM Resolved 111 Pending 8/15/17 2:00 AM Resolved 111 Resolved 8/15/17 12:00 AM Assigned 222 (null) 8/16/17 12:00 AM In Progress 222 Assigned 8/17/17 12:00 AM Pending 222 In Progress 8/18/17 12:00 AM In Progress 222 Pending 8/19/17 12:00 AM Pending 222 In Progress 8/19/17 4:00 PM Resolved 222 Pending 8/19/17 12:00 AM Assigned 333 (null) 8/20/17 12:00 AM In Progress 333 Assigned 8/21/17 12:00 AM Pending 333 In Progress 8/22/17 12:00 AM Resolved 333 Pending

#1. I want to be able to find the most recent Audit Date for each Incident ID's Status.  Expected result:

 Audit Date Current Status Incident ID Previous Status 8/10/17 5:00 PM Assigned 111 Assigned 8/12/17 12:00 AM In Progress 111 Assigned 8/13/17 12:00 AM Pending 111 In Progress 8/15/17 2:00 AM Resolved 111 Resolved 8/15/17 12:00 AM Assigned 222 (null) 8/18/17 12:00 AM In Progress 222 Pending 8/19/17 12:00 AM Pending 222 In Progress 8/19/17 4:00 PM Resolved 222 Pending 8/19/17 12:00 AM Assigned 333 (null) 8/20/17 12:00 AM In Progress 333 Assigned 8/21/17 12:00 AM Pending 333 In Progress 8/22/17 12:00 AM Resolved 333 Pending

Here's my formula:

IF [Audit Date] = {FIXED [Incident ID1], [Current Status]: MAX([Audit Date])}

THEN [Audit Date]

END

#2. I also would like to calculate the difference in time between specific statuses, while taking #1 into consideration.  I.e. time difference in minutes between Pending and Assigned.  I think I need to use the formula in #1 for this.

Here's my formula:

IF CONTAINS([Current Status], "Pending") AND DATEDIFF('minute',[???],[???]) <= 1440

THEN 'Green'

ELSEIF CONTAINS([Current Status], "Pending") AND DATEDIFF('minute',[???],[???]) > 1440

THEN 'Red'

END

Any help would be greatly appreciated, thanks!

• ###### 1. Re: Calculating date differences between multiple records

I could not understand your Second Question. If you can Clarify with an example using Part 1.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 2. Re: Calculating date differences between multiple records

Hi,

I used a table calc in order to find the previous value and calculate the variance.

I also set Time Diff (the table calculation that's calculating the variance) to restart every Incident ID so you get null for every 'Assigned' value.

If you want the values to show for 'Assigned' right click the Time Diff measure in the marks area and select Compute Using -> Table Down.

I also used your logic for the 2nd part to build the color measure.

Let me know if you have any questions.

1 of 1 people found this helpful
• ###### 3. Re: Calculating date differences between multiple records

Thank you Deepak

• ###### 4. Re: Calculating date differences between multiple records

Thank you Andrew