Hey everyone!
I've run into an issue while attempting to create a card to graph a calculated field that involves a case statement and a window function but I'm not sure if my implementation is wrong or if this is a Domo limitation. I have a database that records the Employee's ID, Date, Hour of Availability (24H format) during the day that the employee has input and the availability status (e.g. if Employee 123 is going to work on 10/22/2020 for 3 hours we would see a 1 when he's availabile, 0 otherwise as below):
courier_id | availability_date| Hour | Nr_Hrs_Availibility
123 | 10/22/2020 | 9 | 1
123 | 10/22/2020 | 12 | 1
123 | 10/22/2020 | 17 | 1
124 | 10/22/2020 | 10 | 1
124 | 10/22/2020 | 11 | 0
124 | 10/22/2020 | 13 | 1
What now I'm trying to do is to categorize the employees into the ones that input 8 hours or less, between 9 and 20 hours and more than 20 hours and visualize weekly what percentage of the employee base work within these three ranges. I've created the attached Calculated Field that works and populates my data table correctly but when I attempt to do a COUNT(DISTINCT 'courier_id') in order to avoid double counting the Employee IDs that are repeated and attempt to visualize it (be it a Table, Graph or anything) I get a message saying "An issue has occurred during processing. We are unable to complete the request at this time." Do you have any idea why this might be happening?