Announcements
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER
cancel
Showing results for
Did you mean:
Creator II

## How do I get count of the hours worked by a person to color code it?

Hi guys,

Here is a sample data. I have multiple people who worked at different hours of the day.

The numbers 26, 56, 15 are by the formula count(distinct CASE#).

The header for date time (7/17 8PM, 7/17 9PM...) is DATE_TIME_CST.

I am trying to color code the cells based on a target of 29. I know the logic for it, but I am unable to figure out how to get the count of hours they worked in a day.

The logic I am using is

if(Count(distinct CASE#)>=(6.5/ Count(distinct DATE_TIME_CST))*29,lightGreen(),lightred())

In the above logic, Count(distinct DATE_TIME_CST) is definitely wrong as for each column, the distinct count would be 1. But I am looking for the total count of hours worked by the person in terms of DATE_TIME_CST i.e 3 in this case.

What would be the correct formula?

1 Solution

Accepted Solutions
MVP

Try this

if(Count(distinct CASE#)>=(6.5/ Count(TOTAL <Name> distinct DATE_TIME_CST))*29,lightGreen(),lightred())

4 Replies
MVP

Try this

if(Count(distinct CASE#)>=(6.5/ Count(TOTAL <Name> distinct DATE_TIME_CST))*29,lightGreen(),lightred())

Creator II
Author

Guess DISTINCT is giving the error, Sunny.

MVP

You forgot to add <> around USER_NAME

Creator II
Author

Oops, right! Thanks a lot as always

Community Browser