How to use distinct count of multiple fields?

[JoeHubner]

I have data that looks like the following:

Week               Employee        Project#

6/3/2022         A                      001

6/3/2022         A                      002

6/10/2022       A                      002

6/10/2022       B                      002

6/17/2022       A                      003

6/17/2022       B                      001

6/17/2022       B                      002

6/24/2022       B                      001

I would like to get a count of the total of the number of distinct weeks that employees appear in the data regardless of how many projects they have an entry for .  So, for the above the count should be 6 as below:

6/3/2022 > Employee A > Count=1

6/10/2022 > Employee A and B > Count=2

6/17/2022 > Employee A and B > Count=2

6/24/2022 > Employee B > Count=1

Is there some way I can use multiple fields in Distinct Count to accomplish this?

[JoeHubner]

That just returned each of the values of Employee and not the total number of weeks the total employees appears in the data.

[bowesmana]

add by Week to the stats command, i.e.

| stats values(Employee) as EmployeeID dc(Employee) as EmployeeCount by Week

Note that will not actually do by a 7 day period, but will split by the 'Week' field.

If you want to make it really split by a 7 day window in case your dates are not always 7 day periods, then you will have to parse time and use the stats + bin command to group by a real week.

[JoeHubner]

Making progress on this.  My data now show the correct EmployeeCount for each week.  How can get the total employee count over each month?  That would be the final number that I would need.  Thanks for the help so far.

[bowesmana]

The solution to getting the appropriate time spans is to use the bin command along with stats by _time, 

| eval _time=strptime(Week, "%m/%d/%Y")
| bin _time span=1mon
| stats values(Employee) as EmployeeID dc(Employee) as EmployeeCount by _time

The span=1mon will case the date, as specified by the original Week value to be aggregated based on the month. The alternate is the string based approach

| eval Month = mvindex(split(Week, "/"), 0)
| stats values(Employee) as EmployeeID dc(Employee) as EmployeeCount by Month

Note that this would not handle > 1 year, as the month would duplicate.

[yuanliu]

The actual solution will depend on how you want to list results.  Here's an idea

| stats values(Employee) as EmployeeID dc(Employee) as EmployeeCount by Week
| eval Month = mvindex(split(Week, "/"), 0)
| eventstats sum(EmployeeCount) as EmployeeMonthlyCount by Month

As bowesmana noted, Week and Month are not based on time here, just based on strings.

[yuanliu · Accepted solution]

Something like this?

| stats values(Employee) as EmployeeID dc(Employee) as EmployeeCount
| eval EmployeeID = mvjoin(EmployeeID, ", ")