2 Replies Latest reply: Apr 4, 2017 11:50 AM by Mohammed Al Radi

How can I count the total number of distinct recipients that worked a total of 0-20 hours in 2015?

Mohammed Al Radi Apr 4, 2017 7:15 AM

I am trying to say 'count the total number of distinct recipients that worked a total of 0-20 hours in 2015.' The formula I have only counts the total number of rows (in the excel document) that have less than 20 hous (i.e lets say 'Matt' is recorded in 2 rows, with each row having 15 hours...the formula below counts both those lines as if they were distinct people even though they correspond to the same perosn who would have well over 20 hours).

Count (distinct {$<[Total Hours]={"(>=0 <=20)"}, Year={'2015'}>} Receipient)

I am hoping i am just missing something simple! I have been scouring forums and trying different things. Thank you for your time and consideration.

Re: How can I count the total number of distinct recipients that worked a total of 0-20 hours in 2015?

Nicole Smith Apr 4, 2017 11:40 AM (in response to Mohammed Al Radi)

Try this (you need to sum the number of hours at the recipient level):

count({<Recipient={'=sum({<Year={2015}>}[Total Hours]) <= 20'}>} distinct Recipient)

If, for some reason, you have negatives or need to include the >=0 portion, you could do so like this:

count({<Recipient={'=sum({<Year={2015}>}[Total Hours]) >= 0'}*{'=sum({<Year={2015}>}[Total Hours]) <= 20'}>} distinct Recipient)
Like Show 1 Like (1)

Actions
- Re: How can I count the total number of distinct recipients that worked a total of 0-20 hours in 2015?
  
  Mohammed Al Radi Apr 4, 2017 11:50 AM (in response to Nicole Smith )
  
  Amazing! Thanks Nicole
  
  Like Show 0 Likes (0)
  
  Actions

Go to original post