I am having an issue with Count(Distinct(Collect returning 1
The formula doesn't error out but produces a 1:
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY() - 3), {Installed_Date}, MONTH(@cell) = 7)))
I have a list of servers, the hotfixes, and the date the hotfixes were installed. So 1 server may have multiple hotfixes applied during a month/year. I am trying to count the distinct number of servers that had a hotfix applied during a certain month/year. Any idea where I am going wrong? I tried to look through several responses of the same question but still no luck.
Best Answer
-
Let's build on the formula, one term at a time.
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell), {Installation_Date}, MONTH(@cell) = 7))
Answers
-
Try:
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY())- 3), {Installed_Date}, MONTH(@cell) = 7)))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark -
I dropped it in as is, I got #UNPARSEABLE.
I also tried to remove the extra parenthesis at the end, but it still returned 1.
I also tried removing the parenthesis after the 3:
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installed_Date}, MONTH(@cell) = 7)))
And still got 1.
-
Hi Brandis,
You're using different names for your [installedOn] column reference - installation_date and installed_date. Confirm both ranges go to the same date column.
Count(distinct(collect())) should work. Try adding join to put a comma between each value before counting:
=COUNT(JOIN(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installed_Date}, MONTH(@cell) = 7)),", "))
Any luck?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Sorry, that was a typo on my end. I went back to verify the correct reference for the year and month {Installation_Date}. I also confirmed that there are 17 unique servers with multiple hotfixes with an installation date of July 2018 in my sheet.
The following is what I've tried and all produce 1.
1)
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY() - 3), {Installation_Date}, MONTH(@cell) = 7)))
2)
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installation_Date}, MONTH(@cell) = 7)))
3)
=COUNT(JOIN(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installation_Date}, MONTH(@cell) = 7)), ", "))
When I do just a count of the distinct servers in my entire list, =COUNT(DISTINCT({Server})), it gives me the correct number of unique servers.
Of course all but a couple of the servers have multiple dates other than the July 2018 date...not sure if this affects anything.
-
Hello @Brandis Smith
Did you get your formula working? I wondered, for trouble shooting purposes, what this produces
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell)))
-
Hi @KDM - No, unfortunately, I did not get the formula to work, yet.
The formula you provided gives me the total count of unique servers in my list (61).
-
Let's build on the formula, one term at a time.
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell), {Installation_Date}, MONTH(@cell) = 7))
-
Hi @KDM!! Thank you so much, I went ahead and added the Year portion to the formula and it worked!!
=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell), {Installation_Date}, MONTH(@cell) = 7, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3)))
Thank you so much!! Now I will see if I can add some other conditions to ensure it remains automated as the years change!
-
Glad it worked. The working formula tells me that some of your 'dates' aren't dates- we excluded them from being examined with the condition of 'ISDATE'. You could add a temporary checkbox helper column to your source sheet with the formula IF(ISDATE([Installed On]@row), 0,1). This will add a checkmark to the 'non-dates'. You could then evaluate those rows to see why there is a date error.
cheers,
Kelly
-
@KDM, interestingly, I just did the check and all of them are dates...but, I would have never thought to use ISDATE.
-
hmm. That is interesting. I'm glad it worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!