Hi All, I need help counting streaks in my app. I have a trips table and have columns for date, trip event etc. What i ciuld like to count is the number of days a driver has gone without incurring a trip event. I trip event would be a mild barking or mild excelleration for which points are deducted.
I am trying to calculate their longest streak and their current streak. I have watched @Robert_Petitto video however that seems to only be for the date column. I need to use the date column as well as another column. Unless my thinking is incorrect. TIA
So is it correct that the “other column” you mention above is for some sort like an event type? Like you have multiple types of event and a trip event is one of them?
Yes the other column is the trip event column that stores an event like Mild Braking or Mild Acceleration. A third column then stores the event deduction points. E.g Mild Braking is 4 Points deducted from their daily drive points of 60.
Create a Query column in your Trips table that targets the same table and has the following filters:
– User is This Row → User
– Date is after This Row → Date
– Points Deducted is greater than 0
Add a Single Value column that takes the earliest date from the Query column
Use a Math column to the Date in each row from the Single Value Date (Trunc(svDate - rowDate))
The result of the Math column should be the streak based on the row date
To get the current streak for each Driver, you would use the value in the row that corresponds to their most recent Trip, and add that to the difference between the row date and the current date.
Of course the above is untested, but I beleive the logic is sound.
I just need some clarity on what the numbers mean in the StreakBasedOnRowDate?
If I have followed you correctly. This is the output. I am not sur ewhat it means exactly. Which number is the start of a new streak?
Is this output indicating the longest streak?
StreakBasedOnRowDate should be their streak as it would have been on the Date of that row.
To get a Users longest streak, create a Query or Multiple Relation from the Users table that matches their email and/or UserID in both tables, then use a Rollup->Maximum of the StreakBasedOnCurrentDate.
Is this counting their daily streak or their trip streak? Meaning is it counting the number of days between a trip event or the number of trips between a trip event?
[/quote]
Start with a Query column in your Users Table that targets the Trips table, and apply the following:
– Filter: User is This row->User
– Order by: Trip Date (descending order)
Use a Single Value column to get the first Trip Date from the Query column (this should give you the Date of their most recent Trip)
Use a Single Value column to get the first StreakBasedOnRowDate from the Query column (this should give you the value of their streak after their most recent trip)
Calculate the number of days since their most recent Trip: Trunc(Now-LastTripDate)
Add this to the Streak value as it was after their last Trip (value obtained above)
And the result of that should be their current Streak.
Ok, i believe it is working as expected. I made a change here as your instruction were to get the first trip date instead of the last trip date, meaning the date of the most recent trip.
The results. I had an event on the 23 October 2023 so 2 days looks correct.
hmm, did you add the ordering to the Query column?
I know that the Order By clause on the Query column can be unreliable at times, so quite possibly that didn’t work. If the Query is returning rows in sheet order, then using Last should be fine as long as you know that trips will always be added in chronological order.
, I missed that step. I have changed it as you instructed.