Analytical Need
A lot of business use cases revolve around utilization of resources and their productivity where the resources could be their inventory, machines, carrier or the human resources of an organization i.e., employees. That’s when monitoring the weekly hours of employees comes in very handy and more importantly to keep track of the weekly overtime per employee as well as for the whole team/department/organization.
Modeling Challenge
Whenever the employee working hours data is captured, it is done on a daily basis and a lot of times we see that the employees split their daily hours between different job types or different facilities or different natures of ticket or different customers so that the capture of data is done at the most granular level. In order to measure the overtime, we need to first calculate the hours worked per week per Employee and then compute the overtime per Employee before we sum it up to find out the overtime of all employees for that week.
If we pre-aggregate the weekly hours per employee, then we would lose out on filtering ability by the teams or job types or tickets which would prevent us from computing the overtime hours per facility/job type/nature of ticket against their own threshold. Most of the companies may have a common threshold of 40 hours / week for full-time employees overall but there could also be other thresholds e.g., facility-wise threshold of 10 hours per facility. Aggregated tables will eliminate the dynamic slicing of the data hence, then we need to use the employee hours table at the most granular level (per day hours split into each per facility/job type/nature of ticket) and make use of the front-end capabilities to compute the overtime hours per employee per day
Solution
We are going to consider a simple example to understand the solution: Here is a simple table that shows the hours of work logged by two different employees.
Let us assume that the threshold for overtime is 40 hours per week as Abraham and Bob are full-time employees. So, if we have to find the overall overtime (both these employees combined), we cannot just sum the total hours of the 2 employees and subtract (40*2=) 80 to get the overtime, which will be like,
80 – 80 = 0 OVERTIME HOURS
The above calculation is clearly incorrect as we could simply eyeball and tell that Abraham has worked less than 40 hours that week and Bob has worked 1.5 hours overtime which would result in 1.5 total overtime hours when both these employees combined.
In order to compute this correctly, we need to compute the following by each employee: Add the working hours separately, then subtract 40 from that, and then if the result is positive, we need to add that to the overall overtime hours.
If we are doing that on an indicator widget where the context of ‘Employee Name’ is not available unlike the pivot table, this could be possible by injecting the context of ‘Employee Name’ while computing the overall overtime. To inject a context (group by), we need to use the concept of multi-pass aggregation.
As a part of the first step, create the simple overtime formula and star it as ‘Overtime’ using the CASE WHEN statement of the formula editor and visualize that in a pivot table with Employee Name in the rows:
Now, let us use multi-pass aggregation and inject the context of Employee Name into the calculation of Overall Overtime using the saved formula ‘Overtime’. Create that in an indicator widget:
Concept: We need to compute this ‘Overtime’ for each Employee and then Sum all of Overtime hours to get the Overall Overtime.
Formula:
Then, you will look at the correct value:
In contrast, if you just use the saved formula ‘Overtime’ in the indicator widget without the context, you will end up seeing incorrect value:
Since we are already filtering by a certain week at the dashboard level, we only have to inject the context of employees (have used Emp_ID) to compute the overtime per employee and then finally add it. The important part of the entire process is using the right context and aggregating another sum. If you look closely, the above was an example of Sum of Sum – Sum of hours per employee where (Sum of Hours – 40) if the employee has any overtime hours that week => Sum ( [Employee], Sum). If we could understand how this simple example works, then we could apply this and scale the usage of injecting context to implement any complex use case – this way we could eliminate the approach of pre-aggregated tables in the Elasticube but perform dynamic analysis using the frontend formula editor.
