on 09-10-2015 4:21 PM
I'm trying to pull a report with Average Hydrant Inspections completed per day.
I've got the Report Grouped by Date and I have my Distinct Count for # Completed.
The problem is to get the Average I need a Distinct Count of the # of Days and you can't do a Sum on a Group (it isn't an option).
Every Date in the database has time included in the Date so every entry is actually Distinct and my Distinct Count comes up the same as my Total # of Inspections.
I'm looking for some ideas on how to get around this. I thought about using the TRUNCATE but from what I see that is no different then Filtering the Column and I get the same answer. I've done the math Manually for now but would like to get this to work as we could use this for just about every Feature we use. If I could get the report to work, I could load it on the Superintendents Desktop and they could run it whenever they want.
I appreciate any suggestions anyone might have.
Thanks,
Ellen
Hi Ellen,
Create a formula with this code:
Date({Date_time_field})
Insert a 'Distinct Count' on this formula field.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you Abhilash! It now works. You are the best!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It's fairly easy to convert a date-time to a date, so your could do something like this:
DistinctCount(Date({date field})) to get the total for the report or DistinctCount(Date({date field}), {group field}) to get it for a group.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.