COUNT(DISTINCT(COLLECT to count unique values based on criteria
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?
Best Answer
-
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.
Answers
-
What column type is housing the dates?
-
It is a Date column.
-
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?
-
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().
-
Hmm... It should be working with what you have.
Exactly how are the dates entered?
-
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]).
-
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"?
-
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.
-
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?
-
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.
-
whoops, just saw the manually entered answer. I'm glad it's not a datestamp issue
Help Article Resources
Categories
Check out the Formula Handbook template!
