Count of Distinct Users in a Date Range

khall

I am working on a metrics formula. It worked when I wrote it, I was using the data in a graph, which worked, I changed the graph type, and suddenly the data went to zero. Now, all o my formulas set up like this are all returning a value of 0. Trying to determine what went wrong.

I am attempting to count the number of distinct users within a given date range. I'm using this formula:

=COUNT(DISTINCT(COLLECT({SystemUser}, {Date}, AND(@cell <= [Week Ending]@row , @cell >= ([Week Ending]@row - 6)))))

SystemUser is a cross sheet reference to a text list of system users.

Date is a cross sheet reference to a date formatted column

Week Ending is a date formatted column on the current sheet.

I have already tried adding a helper column to change both the Date column and the Week Ending column to text fields (=Date@row + "") and updated the main formula to use those helpers. That returned a value of 1 which is not correct at all.

Any ideas about how to troubleshoot? Are there errors I'm not seeing?

heyjay

=COUNT(DISTINCT(COLLECT(

{SystemUser},

{Date}, <= [Week Ending]@row  ,

{Date}, >= ([Week Ending]@row  - 6) 

Can you try this one. Im not sure about the minus six. I thought of splitting the Date in two.