Select Distinct Cars By Max Inpsection Date
Need to count the number of distinct Cars by Max Inspection Date and report on their Inspection Due Date. On my Metric sheet I have a below metric defined but realized the counts were off because the Cars being counted needed to be distinct :
Current Formula:=COUNTIFS({STATS}, "In Service", {Due Date}, MONTH(@cell) = $[Month#]
Data Sheet
Based on the Data Sheet, the total cars Due for Inspection in July is 1 (Car 100). Car 101 need to be counted in August based (Max(Due Date).
I tried INDEX(COLLECT...but getting lost because I think I need to first collect distinct cars based on max date and then compare to the Month@cell.
Thanks in advance,
Answers
-
-
Yes, I tried and several combinations for exampe - =COUNT(DISTINCT(COLLECT({Cars}, {Due Date5}, MONTH(@cell) = $[Month#]@row))).
-
I'm not sure I understand why car 101 is being pushed to August when it has the same due date as car 100. Or are you trying to count how many unique days there are in July that have due dates on them?
-
The max inspection date overrides the earlier date. Normally inspections occur every 90 days, sometimes cars are spot checked which will generate a new inspection renewal date.
-
Another option would be to collect the distinct cars and add a helper column Active/Inactive to switch off the earlier inspection???
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!