Counting Man-Days

[johnmorgan]

I have a Job Id, a Date and a User.  

Job	Date	User
1234	2019-04-04	Fred
2345	2019-04-01	Sam
5678	2019-04-04	Fred
3456	2019-03-15	Fred
6789	2019-04-01	Sam

Multiple jobs may be done on the same day by one ore more users.

What expression will let me count Total Man Days?

[JordyWegman]

Hi John,

What do you mean with Total Man Days?

• The total amount of jobs done per man for each day?
  • Number per man
• Total amount of days all the men have been working?
  • One number

Jordy

Climber

[johnmorgan]

The total number of days that have been worked by a person.  I.e. a count of distinct pairs of (user, day).  Or to put it another way the sum total of the number of days that each user has performed one or more jobs.

The model I presented is grossly over simplified.  So maybe a little more information will clarify.  What I ultimately have is a list of tasks performed by users with a timestamp (the timestamp has already been broken down into its constituent parts so I can count distinct days).  The tasks are coded, and each code has a relative value unit (a unit that defines the complexity of the task).  That unit is used to calculate performance of a user.

What I need to find is the average number of the relative value units per user per day for the current selection (as well as a number of other statistics).  I have everything I need, except the total man-days to calculate the average (oddly I was able to to this on a per user basis with no trouble, it is just the aggregate that is giving me problems).

One other difficulty, this data is represented from several SQL tables that are loaded into a data set with several joins, so there are possibilities of data replication in all fields, and I do not want to count anything twice.

[JordyWegman]

Hi John,

If your calculation should be the total number of days that have been worked by a person you can do the following:

Concatenate the two fields:

Date &'|'& User as %Field

 Then do the following formula:

Count(Distinct %Field)

If you choose User as Dimension you will get total number of days that have been worked by a person.

Jordy

Climber

[johnmorgan]

I didn't think about concatenating the data load script, I will give that a try.  Just out of curiosity, Is there a way to do that type of projection within the sheet expression?  I was attempting to do a count of distinct values of an aggregate of date and user, however that consistently returned 0 (I understand this may not be answerable without considerably more information about the model, I am able to associate user/job/date in other locations, so I can figure out why that wouldn't work).

Thanks,

John

[JordyWegman · Accepted solution]

Hi John,

This should also be possible try the following:

Sum(Aggr(Count(Distinct User&'-'&Date),User))

 Don't aggregate by date because you want to know how many days each person has worked. Otherwise you also need Date in your table as a dimension.

Jordy

Climber

[johnmorgan]

This worked perfectly. Thanks much.