Forum Discussion

SamBrown17's avatar
SamBrown17
Helper 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...
Need Help
  • 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
SamBrown17's avatar
SamBrown17
Helper I
3 years ago

Late to come back but I cannot find out why, but when I try and finished this it results in "The end of the input was reached" and it puts 3 closed brackets on the end every time I hit enter