From accumulate to increments

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!

1 Like

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!