I have a dataset that contains the repair date of the vehicle, the arrival date for repair and the vehicle id. The question is to show that a particular vehicle returned for repair within 30 days of prior repair. I am thinking of adding a calculated field that would have the days from the prior repair date. I tried using the lag function to get the previous repair date and then do the difference between the current date and the prior repair date but got some errors. Could someone please suggest what function can be used here?
Lag seems to work only for measure, not on date fields. Is there any other way to get the date from previous repair for a vehicle?
Iām not sure lag is working with dimensions as a first argument.
Maybe you could use
To get the last date?
BR
Thanks, @ErikG - I understood that Lag cannot be used with /dimensions. I think you by mistake pasted the same Lag function as an option. Could you please tell me again which function you are suggesting I use
Sorry. I updated the post.
I tried it this way
lastValue(
[{REPAIR DATE}],
[{REPAIR DATE} ASC],
[
{EQUIP ID}
]
)
But getting the error
My dataset is like
| vehicle | report date |
|---|---|
| A12 | 2023-01-20 |
| A12 | 2023-03-21 |
| B12 | 2023-02-19 |
| A12 | 2023-06-05 |
| B12 | 2023-04-21 |
And I want the result to be
| vehicle | report date | previous report Date |
|---|---|---|
| A12 | 2023-01-20 | |
| A12 | 2023-03-21 | 2023-01-20 |
| B12 | 2023-02-19 | |
| A12 | 2023-06-05 | 2023-03-21 |
| B12 | 2023-04-21 | 2023-02-19 |
Hello @pankaj_kushwaha and @ErikG !
@pankaj_kushwaha were you able to find a work around for this or was @ErikGās suggestion above helpful? If so could you either post your solution or mark one of the posts above as a solution to help the community?
Hello @pankaj_kushwaha and @ErikG !
It has been some time since there was activity on this thread but we would still like to help find a solution.
If there is not activity on this in 4 days this topic will be archived.
Was able to get it going by creating a duplicate date field, and using that in the lag function.
1 Like
Trying to calculate the second last transaction date using this function with duplicated field, however, get this error
Hello everyone!
Based on the data in your solution, can I create a calculation of the difference in minutes between the two dates?