Get the total count of distinct dates ignoring the date time

Greg Watkins
Greg Watkins used Ask the Experts™
I have a crystal report that has date and time (Queue_Member_Events.Event_Date) fields in it. I need to get the total count of the distinct dates in the report ignoring the date time. For example...

DateTime = '2019-06-01 10:13:00'
DateTime = '2019-06-01 11:03:00'
DateTime = '2019-06-01 13:43:00'
DateTime = '2019-06-01 14:05:00'
DateTime = '2019-06-01 18:35:00'
DateTime = '2019-06-02 10:01:00'
DateTime = '2019-06-02 10:23:00'
DateTime = '2019-06-02 14:56:00'
DateTime = '2019-06-02 16:28:00'

TotalDayCount = 2

It seems like this should be easy using the DistinctCount function in a formula as shown below but when I try to save this formula I get an error that the DistinctCount function requires a field. What am I missing?

TotalDayCount := DistinctCount(Date({Queue_Member_Events.Event_Date}));
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Create a formula that returns just the date.
Then, reference that formula in the DistinctCount()

In other words, break it into 2 steps to allow the argument for the DistinctCount to be a reference to an object rather than an expression.


Thanks for the quick reply Ido. I thought about that as well but I'm a little confused how to create the formula for the dates since I need it to include all of the dates in the report so that the formula to get the Distinct Count covers all of the dates. Can you enlighten me?
Professor of MIS at Penn State Erie and Owner, Millet Software
{@OnlyDate} formula is simply Date({Queue_Member_Events.Event_Date})

Then, your DistinctCount expression becomes: DistinctCount({@OnlyDate})

No expression is allowed as an argument to DistinctCount(). But passing in a reference to formula name is fine.


That worked. I guess I was just over thinking it.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial