Hi, I have a table with the following fields
Truck#, date, Odometer
In this table there is a record for each day, truck and the total kilometrs (Odometer)…
The question is, how can I know hoy many km each truck covers each day?
Approch 1:
Km day5 = Odometer day 5 - Odometer day 4
How can I do this in QuickSight?
Approach 2:
Some days the driver don’t record the ODO info, so I don’t have the info…
km day 8 = (Odo day 8 - Odo day 5) / (day 8 - day 5)
How can I do this in QuickSight?
I would make a lag field and subtract the previous value like such (substitute fields and aggregations that make sense to you).
{previous day} = lag(count({session_id}),[{arrival_timestamp} ASC],1,[{client_id[users]}])
difference = count({session_id})-{previous day}
For your second approach you would do a similar lag function but return the date and take the difference of dates.
previous day date = lag({arrival_timestamp},[{arrival_timestamp} ASC],1,[{client_id[users]}])
dateDiff({previous day date},max({arrival_timestamp}),‘DD’)
date diff previous day = dateDiff({previous day date},max({arrival_timestamp}),‘DD’)
Your final calculation would be
avg difference = difference / {date diff previous day}
Let me know if that’s what you’re looking for!
Hi @Lrrc. Did Max’s solution help? I am marking his reply as, “Solution,” but let us know if this is not resolved. Welcome to the QuickSight Community, and thanks for posting your questions on the community Q&A Forum!
Hi @Max, yes its worked! Thanks a lot!
For your second approach you would do a similar lag function but return the date and take the difference of dates.
previous day date = P({arrival_timestamp},[{arrival_timestamp} ASC],1,[{client_id[users]}])
dateDiff({previous day date},max({arrival_timestamp}),‘DD’)
date diff previous day = dateDiff({previous day date},max({arrival_timestamp}),‘DD’)
Your final calculation would be
avg difference = difference / {date diff one day}
