Forum Discussion

SamBrown17's avatar
SamBrown17
Icon for Helper I rankHelper I
4 years ago
Solved

How to calculate Date diff with data setup

Hi Team,   I have the example data below,   I have a database full of record types which are jobs in my system, I want to calculate the average time or date difference between a set sequence of t...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    SamBrown17 Right, it's the same principle but yours will look something like this:

    Column = 
  VAR __record = [record_id]
  VAR __type = [event_type]
  VAR __current = [created_at]
  VAR __currentStatus = [To (Status)]
  VAR __previous = 
    IF(__type = "Job status updated",
      SWITCH(__currentStatus,
        "Scheduled",MAXX(FILTER('Table',[record_id] = __record && [From (Status) = "Book" && [To (Status)] = BLANK(),[created_at]),
        "Site Attended",MAXX(FILTER('Table',[record_id] = __record && [From (Status) = "Book" && [To (Status)] = "Scheduled",[created_at])
      )
    )
RETURN
  __current - __previous
v-henryk-mstf's avatar
v-henryk-mstf
Icon for Community Support rankCommunity Support
4 years ago

Hi SamBrown17 ,

 

Whether the advice given by Greg_Deckler  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry