Applying complex billing rules to patient-level aggregated data


My goal is to be able to display, in a single KPI visual, total billable time across all patients. Let me explain the problem.

Nurses perform tasks associated with particular patients. These tasks have a duration, how long it took the nurse. Tasks can be of three different types, lets just say type, 1, 2, and 3. Every month we bill patients for this task time according to specific rules related to the task types.

Type 1 is billed in discrete 20 minute intervals. I.e. 19 minutes of type 1 time is unbillable, but as soon as they hit 20, they can be charged one unit. Again, 39 minutes would equate to only 1 charge, but as soon as they hit 40, they can be charged two units. So 19 minutes is “unbillable time” because we cannot charge the patient anything for this time. Similarly 39 minutes equates to 20 minutes of billable time and 19 minutes of unbillable time because we cannot charge again until 40 minutes.

Type 2 is billed in discrete 20 minute intervals, same way as Type 1. And Type 3 is billed in discrete 30 minute intervals.

At the end of the month, any given patient has a certain amount of time from Type 1, Type 2, and Type 3. I would like to aggregate total minutes by patient, find the total billable time by applying the rules explained above, and then display in one visual the total billable time across all patients. Rows in our dataset are the individual tasks, so one patient relates to many rows. There is a single column for task duration and then another column that specifies task type.

Any help on how to approach this sort of problem would be greatly appreciated!!


Hi @connoranderson .
The way I would approach this is by creating a calculation in the dataset that removes the unbillable hours. You can do this by rounding down by the intervals you mentioned. In the analysis, you can simply sum this field.I believe the below example may work for you. I will mark this as a ‘Solution’. If you need something else, please uncheck the solution box and reply back. Happy dashboarding!







Thank you for your reply! I think this is definitely thinking in the right direction. However, if I were to apply this at the dataset level (where rows are the individual tasks, not the patients), the vast majority of the individual tasks are under 20 and 30 minutes. So if a task is 1 minute, divide by 20 and floor would return 0. What we need to look at is total minutes per patient per type. So after aggregating total minutes by patient, split that out to look at their total type 1, type 2, and type 3 mins, find how much is unbillable for each of those types, and then sum that unbillable total across all patients.

I hope I’m being clear, let me know if I’m missing anything.

Hi @connoranderson,
I still think this is the right solution. You would have a new column ‘billable hours’ and the original column ‘total hours’. You can then subtract the two to get the difference of non billable hours. This would give you all three columns of data that you would need. Then you can aggregate as you need.

Hi, @connoranderson. We hope that Doug’s solution worked for you. Let us know if this is resolved. (And if it is, we’d love it if you could mark his answer as a “Solution.”) Thanks!