Duplicate rows

AppSheet AppSheet Q&A
1

Hi,

I need help writing an expression that counts the rows in a table, but excludes rows from the count when there is a duplicate date in the date column. I also need to further constrain the results by the Client ID for the current record, i.e. [_THISROW].[Client ID] = [Client ID]. I can do one or the other, but I haven’t figured out an expression that will do both.

Thanks!

2

Dave:

excludes rows from the count when there is a duplicate date in the date column

Meaning only count the first row with a given date, or omit any row with a date that occurs more than once?

3

Correct

4

Count of distinct date-column column values in the table table from rows for this client:

COUNT(
  SELECT(
    table[date-column],
    ([_THISROW].[Client ID] = [Client ID]),
    TRUE
  )
)
1 Like
5

I’ll give that a shot, thanks!

1 Like
6

Looks like that did the trick, thanks again.

1 Like
7

Hi Steve,

Follow up question: I need to add another constraint to this expression, which I attempted to do, like this:

COUNT(SELECT(Invoice Detail[VisitDate], AND([_THISROW].[Client ID] = [Client ID], [_THISROW].[User ID]=[User ID], TRUE)))

However now it is no longer omitting rows with a date that occurs more than once. I’m using this in a virtual column and need a formula that counts only once each date that a user billed to a client (sometimes more than one user bills in a given day, sometimes the same user more than once). Any thoughts on how I can make this work? Thanks!

8

Your expression on the first line, my suggestion on the second:

COUNT(SELECT(Invoice Detail[VisitDate], AND([_THISROW].[Client ID] = [Client ID], [_THISROW].[User ID]=[User ID], TRUE)))
COUNT(SELECT(Invoice Detail[VisitDate], AND([_THISROW].[Client ID] = [Client ID], [_THISROW].[User ID]=[User ID]), TRUE))
1 Like
9

It’s still counting duplicate dates. Could it be an issue with the way the date is formatted?

10

If the dates have different formats, sure, that could be an issue.