Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

malradi88
Contributor

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

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.

1 Solution

Accepted Solutions

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

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)

2 Replies

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

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)

malradi88
Contributor

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

Amazing! Thanks Nicole

Community Browser