Counting Unique Values, but only if they are within a certain date range

Jack Parry

As the title says, trying to count rows, but only unique values and within a certain date range. Formula I'm trying to use:

=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, AND(MONTH(@cell ) = 8)))

Example Data: Sales Order Number = {SO Number} and Actual Ship Date = {Date}

Any help please

KPH

Hi @Jack Parry

You were almost there. You don't need the AND as there is only one piece of logic for the date criteria (you would use AND if you were specifying a range such as after Jan but before July). But you do need to add an IFERROR to consider rows without a date as having a month of 0, otherwise they confuse the COLLECT.

Try changing

=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, AND(MONTH(@cell ) = 8)))

to

=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, IFERROR(MONTH(@cell ), 0) = 8)))

Let me know how you get on.

KPH

Hi @Jack Parry

You were almost there. You don't need the AND as there is only one piece of logic for the date criteria (you would use AND if you were specifying a range such as after Jan but before July). But you do need to add an IFERROR to consider rows without a date as having a month of 0, otherwise they confuse the COLLECT.

Try changing

=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, AND(MONTH(@cell ) = 8)))

to

=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, IFERROR(MONTH(@cell ), 0) = 8)))

Let me know how you get on.

Jack Parry

@KPH I had to remove the ':{{SO Number}}', but the addition of the IFERROR worked perfectly. Thank you! :)

KPH

Opps, I didn't spot the :{SO Number} part

Well done!

Jack Parry

@KPH Hi, I've taken this one step further to count the user who performed the action.

=COUNT(DISTINCT(COLLECT({Work Order}, {Who}, [User Name]@row, {Date}, IFERROR(MONTH(@cell ), 0) = 9)))

However when I compare that to the total data for the month using this formula:

=COUNT(DISTINCT(COLLECT({Work Order}, {Date}, IFERROR(MONTH(@cell ), 0) = 8)))

Where have I gone wrong here…

KPH

Sorry Jack, I don't understand the problem. What has gone wrong?

Jack Parry

@KPH The total should be either by 5 or 4.
It depends if the calculation looking at the Distinct WO #, User and Date is wrong or the calculation looking at just Distinct WO # and Date is wrong.

My guess is that its the addition of the user that is throwing the calculation off…

KPH

Are you saying that when you include the user the total for one of the users is 1 and it should be 5?

In the two example formulas you shared above, one is for September and one is for August.

If you want to understand the date in the COLLECT, you can remove the COUNT and DISTINCT functions and use JOIN to show you what is being collected. That might help troubleshoot?

=JOIN(COLLECT({Work Order}, {Who}, [User Name]@row, {Date}, IFERROR(MONTH(@cell ), 0) = 9),",")

Jack Parry

@KPH apologies not explaining myself very well.
What I'm saying is the Total = 4 (today now 7), therefore the sum of the users should also add up to 4 (today now 7).
It seems to be 1 out, am I missing a parenthesis on this calculation perhaps? Is the distinct formula also limiting the unique user count?

=COUNT(DISTINCT(COLLECT({Work Order}, {Who}, [User Name]@row, {Date}, IFERROR(MONTH(@cell ), 0) = 9)))

See screenshots below using Join calculation as above:

KPH

If you look at row 1, you have two work orders 02080457, one more pair and two singles. 4 distinct in total.

In row 2, you have 2 distinct work orders.

In row 5, you also have 2 work orders 02080457, then one single. 2 distinct in total.

When you add the totals 4 +2 + 2 you get 8.

However, if you use a count distinct for the total you will get 7 as the two work orders in row 1 that I highlighted (02080457) are the same as the two in row 5. There are only 7 distinct work orders.

Does that make sense?

Either "total" could be valid. It depends on your need.

Jack Parry

@KPH that does make sense.
So if I just wanted a total of the number of distinct orders by all the users? (so 8)

Would it be just a sum of the column or can we define our current formula better?

KPH

If you want a total of the distinct work orders per person (i.e. a sum of the rows above, 8 in the example) use

= SUM(

If you want a total of the distinct work orders in the period (i.e. each work order is only included once, 7 in the example) the

= COUNT(DISTINCT(

Jack Parry

@KPH Is there a way of calculating the distinct values in a date range and of those distinct values calculating the user who performed the action?

=COUNT(DISTINCT(COLLECT({Work Order}, {Who}, [User Name]@row, {Date}, IFERROR(MONTH(@cell ), 0) = 7)))

Maybe using a countif function?

KPH

You can count the distinct work order in a date range but I'm not sure what you mean by "calculating the user who performed the action"