Count of distinct live Projects by Date due to be delivered, and excluding closed projects

Sharon B

Hi all,

Wonder if someone can help please. We have multiple projects running, some of which may have multiple stakeholders involved. In our reporting we have a line per stakeholder per project. Some projects therefore may have one line in a data set, and some may have multiple but they are all linked by a unique project name. See attached mock up data set AINRPV Test Data.

AINRPV Test data.xlsx

We are trying to count up how many projects are due to be delivered in each month of 2023 by activity type, and excluding any closed records. We want to produce a table such as the one attached called activity type by go live date. But we need to ensure that we only count the project name once. So for example Project 28 has 6 lines of data, but we only want to count it once so result should =1.

Activity Type By Go Live Date.xlsx

We do know how to count up how many activities there are by type excluding closed records by using this formula as this works fine:-

=COUNT(DISTINCT(COLLECT({Project Name}, {Activity Type}, Activity Type@@row, {Project Stage}, <>"Closed/Won", {Project Stage}, <>"Closed/Lost")).

However as soon as we try to introduce any variables to count by date as well, the formula doesn't work. We have tried asking it to count between two dates and asking it to count by the month and year fields, but neither work and we just always get the result "1" (which usually means "Computer says no"!). We have tried multiple combinations of formula like examples shown below (we know we'd have to change the dates shown for each month) :

1. =COUNT(DISTINCT(COLLECT({Project Name}, {Activity Type}, "NPM", {Project Stage}, <>"Closed/Won", {Project Stage}, <>"Closed/Lost", {Active Year}, "2023", {Active Month}, "4")))
2. =COUNT(DISTINCT(COLLECT({Project Name}, {Activity Type}, "NPM", {Project Stage}, <>"Closed/Won", {Project Stage}, <>"Closed/Lost", {Active Date}, >=DATE(2023,04,01), {Active Date}, <=DATE(2023,04,30)))

We have tried re-arranging the formula so the asks are in a different order (e.g. putting activity type, then dates, then whether closed etc), but none of those attempts worked either.

I had a similar simpler ask where I wanted to count up types of activity by Team Member, and was having issues with that also. I had used the original formula that works above (in italics) and added a request to also count by activity type at the end, and it didn't work. However, if I moved the activity type question to be asked prior to the request to exclude closed items, it worked! :_

=COUNT(DISTINCT(COLLECT({Project Name}, {Team Member}, Team Member@row, {Activity Type}, "CQI", {Project Stage}, <>"Closed/Won", {Project Stage}, <>"Closed/Lost")))

So I don't know if we are asking the questions in the formula the wrong way round, if the asks are not feasible due to a mix of data types, or if we have to do something completely different to help us get the answer we need. I'm not advanced enough to know how to add in and additional nested if or and ask into this formula as I am not sure at which step in the formula this would need to be added. If anyone could explain what we are doing wrong (in fairly layman terms please!), it would be really appreciated. I need to break the steps down to understand the ask of each part of the formula for the penny to drop sometimes!

Thanking you all in anticipation.

Sharon

Dale Murphy

@Sharon B Just on a flier, you could try looking for the year of {year} and the month of {month} and comparing that to your period of interest:

=COUNT(DISTINCT(COLLECT({Project Name}, {Activity Type}, "NPM", {Project Stage}, <>"Closed/Won", {Project Stage}, <>"Closed/Lost", year({Active Date}), "2023", month({Active Date}), "4")))

*** I don't think you need the quotes around 2023 and 4 but you should confirm that.

dm

Ps. If that doesn't work, I recommend troubleshooting by removing every condition BUT those date related. See of you can get that to work (I suspect your #2 is not returning the expected result because of a date format difference.)

Paul Newcome

@Dale Murphy To include year/month evaluations, you would use

{RANGE}, YEAR(@cell) = 2023

@Sharon B

Give this a try:

=COUNT(DISTINCT(COLLECT({Project Name}, {Activity Type}, @cell = [Activity Type]@row, {Project Stage}, AND(@cell <> "Closed/Won", @cell <> "Closed/Lost"), {Date Range}, AND(IFERROR(YEAR(@cell), 0) = 2023, IFERROR(MONTH(@cell), 0) = 4))))

Although your #2 formula with the DATE functions from your original post seems like it should be working. If the above doesn't work, we may need to do some additional troubleshooting.

Sharon B

Thanks both.

@Paul Newcome That works perfectly thanks very much. And I've learnt something new about the @cell functionality :).

Sharon B

Although....@Paul Newcome I have another formula to do and am getting a one again so if you could advise what I have done wrong here:-

=COUNT(DISTINCT(COLLECT({Project Name}, {Project Stage}, AND(@cell <> "Closed/Won", @cell <> "Closed/Lost"), {Active Date}, AND(IFERROR(YEAR(@cell), 0) = 2023, IFERROR(MONTH(@cell), 0) = 3))))

Have also tried this:-

=COUNT(DISTINCT(COLLECT({Project Name}, {Project Stage}, @cell <> "Closed/Won", Project Stage, @cell <> "Closed/Lost", {CRM, EP & Oncology Ablation Active Date}, AND(IFERROR(YEAR(@cell), 0) = 2023, IFERROR(MONTH(@cell), 0) = 3))))

and this

=COUNT(DISTINCT(COLLECT({Project Name}, {Project Stage}, @cell <> "Closed/Won", AND({CRM, EP & Oncology Ablation Opp Stage}, @cell <> "Closed/Lost"), {CRM, EP & Oncology Ablation Active Date}, AND(IFERROR(YEAR(@cell), 0) = 2023, IFERROR(MONTH(@cell), 0) = 3))))

Am getting the dreaded "1" as a result each time. :(. Any advice of what logic is wrong /what I am not getting here please?

Thanks

Paul Newcome

@Sharon B The first one should be working properly. Have you applied a filter to the sheet to mimic the range/criteria sets to see what pulls through?