COUNT(DISTINCT(COLLECT to count unique values based on criteria

Amelina de la Cruz

Hi,

I tried using the COUNT(DISTINCT(COLLECT function to determine how many unique values based on a Date column. I need to count the unique Batch Numbers that were Received on Date = TODAY.

This is the formula I came up with: =COUNT(DISTINCT(COLLECT({Batch Number}, {Date: Received}, TODAY())))

It returns 1, which I know is incorrect. It seems to work when I add days before or after TODAY. For example, when I count the unique Batch Numbers Received TODAY(-4), it returns the correct number.

Any ideas?

Paul Newcome

That's odd. At least it is working now. It seems like it may have just been a temporary glitch.

It also could have been that certain dates were being stored as text on the back end and only LOOKED like dates, but then as you manipulated the data and changed them from one date and back to another they were then updated to store as dates instead of text.

Paul Newcome

What column type is housing the dates?

Amelina de la Cruz

It is a Date column.

Paul Newcome

How exactly are the dates being entered? Are there any text values in the date column? How are the Batch Numbers being entered? Is it possible that some are numbers and some are text?

Amelina de la Cruz

The batch numbers are a combination of text and numbers. The Date: Received is a Date type column so there is no text. I attached an example of what it looks like.

If the Date: Received were all set to today's date, I'm trying to get the formula return 7 unique values for Batch Number for TODAY().

Paul Newcome

Hmm... It should be working with what you have.

Exactly how are the dates entered?

Amelina de la Cruz

They are manually entered, or whoever is filling it out can use the calendar to fill in dates.

I find it weird that it won't work for TODAY(), but works fine for TODAY([number]).

Paul Newcome

Let's do this just to double check...

Create a temporary column next to the date column and put this in every row:

=IF(ISDATE(Date Column]@row), "", "Not a date")

Do any rows show "Not a date"?

Amelina de la Cruz

All rows are blank.

I tried my original formula again afterwards. It seems to be functioning properly, which is weird because I tried for a few days to get a different number other than "1". But now it's returning the correct number.

Kelly Moore

Hello Amelina

I've been following the conversation. I'm curious, is the Date Received directly entered, or do you extract this date from a datestamp?

Paul Newcome

That's odd. At least it is working now. It seems like it may have just been a temporary glitch.

It also could have been that certain dates were being stored as text on the back end and only LOOKED like dates, but then as you manipulated the data and changed them from one date and back to another they were then updated to store as dates instead of text.

Kelly Moore

whoops, just saw the manually entered answer. I'm glad it's not a datestamp issue