Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnmorgan
Partner - Contributor III
Partner - Contributor III

Counting Man-Days

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

JobDateUser
12342019-04-04Fred
23452019-04-01Sam
56782019-04-04Fred
34562019-03-15Fred
67892019-04-01Sam

 

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

What expression will let me count Total Man Days?

Labels (1)
1 Solution

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder

View solution in original post

6 Replies
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
johnmorgan
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Master
Partner - Master

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

Work smarter, not harder
johnmorgan
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Master
Partner - Master

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

Work smarter, not harder
johnmorgan
Partner - Contributor III
Partner - Contributor III
Author

This worked perfectly. Thanks much.