-1

I have a table of vehicles at service locations showing columns such as DAY, LICENSE, BOROUGH etc. I'd like to add a cross table showing the number of vehicles that have been serviced for 3 weeks or more. I'm not sure what custom expression to use.

Sample data:

Sample data

konrad bachusz
  • 3
  • 2
  • Can you post sample data? Is there a from and to date column? Is there a record for each car for every day its in the shop? Is there a unique ID column? Knowing these things will help come up with a calculated column. I would look into using the `LastPeriods` function. This acts like `Lag` and needs a date column to work. – Mark P. Feb 04 '19 at 17:48
  • @MarkP. sample data added – konrad bachusz Feb 05 '19 at 20:27
  • I don't see DATE, VEHICLE_NUMBER, or MODEL columns in your sample data as you mention in your question. I see no possible way to calculate "serviced for 3 weeks" based on the data you have provided.@konradbachusz – coomie Feb 06 '19 at 13:37
  • @AndrewPruet apologies. I changed the column names and provided more examples – konrad bachusz Feb 07 '19 at 10:33
  • 1
    read this: https://stackoverflow.com/help/how-to-ask I still have no idea how to calculate "been serviced for 3 weeks or more". @konradbachusz – coomie Feb 07 '19 at 15:42
  • "Vehicle number" is probably the License, which should be unique enough along with date. So are you implying there are cars that have been in the shop for 21 days uninterrupted? And if a car was in the shop 20 days, left then came back for 20 more days, that wouldn't show up? – Mark P. Feb 07 '19 at 18:42

1 Answers1

0

I hope your sample data isn't containing a bunch of legitmate license plates. not the most compromising data but I would recommend blacking out or replacing them with test data if it isn't already.

anyway. you're looking for the DateDiff() function. for example:

If(DateDiff('day', Date(DateTimeNow()), [Date]) >= 21, "21 days or more", "less than 21 days")
niko
  • 3,946
  • 12
  • 26