I have the departure date and arrival date for a train in my dataset.
I want to have a new column in my analysis that will have the number of days since the last departure
I tried following the function
dateDiff({ARRIVAL_DATE},lag({DEPARTURE_DATE},[{DEPARTURE_DATE} ASC],1,[{train_id}]))
But I am getting the following error. How should I fix it?
`Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination
Hi @pankaj_kushwaha - Can you please share some sample input and expected output data?
Regards - Sanjeeb
Hi @Sanjeeb2022 ,
Here is the sample input
| Train_id | Arrival Date | Departure Date |
|---|---|---|
| T1 | 2023-01-20 | 2023-01-21 |
| T1 | 2023-03-22 | 2023-01-22 |
| T1 | 2023-06-05 | 2023-06-06 |
| T2 | 2023-02-19 | 2023-02-20 |
| T2 | 2023-03-21 | 2023-03-22 |
| T2 | 2023-03-26 | 2023-03-27 |
Expected Output
Arrival date from the current row MINUS Departure date from the Previous row
| Train_id | Arrival Date | Departure Date | No of days since previous departure |
|---|---|---|---|
| T1 | 2023-01-20 | 2023-01-21 | no previoud departure for T1 |
| T1 | 2023-03-22 | 2023-01-22 | (2023-03-22) - (2023-01-21) |
| T1 | 2023-06-05 | 2023-06-06 | (2023-06-05) - (2023-01-22) |
| T2 | 2023-02-19 | 2023-02-20 | no previoud departure for T2 |
| T2 | 2023-03-21 | 2023-03-22 | (2023-03-21) - (2023-02-20) |
| T2 | 2023-03-26 | 2023-03-27 | (2023-03-26) - (2023-03-22) |
@pankaj_kushwaha why 2023-02-19 2023-02-20 for T2 has not previous departure
but 2023-03-21 2023-03-22 for T2 has departure?
Anyway, you can figure out your logic on your own.
But here is an example
ifelse(dateDiff({Departure Date},{Arrival Date},"DD") >= 1, toString(dateDiff({Departure Date},{Arrival Date},"DD")), 'N/A')
Thanks @neelay ,
I need the departure date from the previous run of the train and the arrival date from the current run. I am trying to find the number of days it took to arrive since it departed. I tried to use the LAG function to get the departure date from the previous run but got the error.
LAG is an aggregated function, while datediff is not. And I don’t think you can mix them. That’s probably what your problem is. I would recommend bringing the previous departure date as a separate column, And then calculate it. KISS (Keep it simple and Stupid)
Hi @pankaj_kushwaha,
You need to ensure that both inputs to the dateDiff function are coming from either aggregated or non-aggregated fields (in your example it would need to be aggregated). I’ve used the sample data that you’ve provided and built a quick sample solution within the newly launched QuickSight Arena. For sake of readability, I’ve separated the calculated field into 3 fields. Using QuickSight Arena, you can dive deep into it my solution directly within the community.
Date diff to previous row
Combined, the calculation for the No of days since previous departure field would look like this:
dateDiff(lastValue({Departure Date},[{Departure Date} ASC],[{Train_id},{Departure Date}]),lag({Arrival Date},[{Arrival Date} ASC],1,[{Train_id}]))
Did this answer your question? If so, please help the community out by marking this answer as “Solution!”. Thanks!
Thanks a lot @Thomas