How can I count distinct values under a same month?

08/27/20
Answered - Pending Review

Hi everyone,

I currently have a data set for which I would like to count all unique values that are under a same month and a same year. For example, the following table shows two duplicate values (TR ID's) on two different dates. In this case I would only like to count the unique TR ID's for the month of August, 2020 (which should be 6):

I have been trying to use the =COUNT(DISTINCT(COLLECT(.....))) expression, however, it returns "1", when it should count "6".


Thanks in advance,

Carolina

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try something like this...

    =COUNT(DISTINCT(COLLECT([TR ID]:[TR ID], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2020))

    thinkspi.com

  • Hi Paul, I tried re-creating this formula for my purposes and I'm getting an "#unparseable" when I adjust the equation for my needs. The following formula should return "2":

    =COUNT(DISTINCT(COLLECT([Patient number]1:[Patient number]3, [Dosing date]1:[Dosing date]3, AND(IFERROR(MONTH(@row),0) = 1, IFERROR(YEAR(@row),0) = 2022))))

    Can you point out what I'm doing wrong? Thank you!


  • Hi @AHANSON

    Instead of using @row inside of your MONTH and YEAR functions, you'll want to use @cell.

    Try:

    =COUNT(DISTINCT(COLLECT([Patient number]1:[Patient number]3, [Dosing date]1:[Dosing date]3, AND(IFERROR(MONTH(@cell),0) = 1, IFERROR(YEAR(@cell),0) = 2022))))

    Cheers,

    Genevieve

Sign In or Register to comment.