Hi,
I'm currently working on creating a calculation for "Actual working hours" by "Standard working hours", and was wondering if you had any input to my case, or would share how you would have done it.
Data:
'EMPLOYEE' - table with all data related to the employees.
EMPLOYEE[ID] - Unique ID for the employee
EMPLOYEE[NAME] - Name of the employee
EMPLOYEE[STDHRS] - this tells how many hours the employee are required to work each week
'PROJTABLE' - "General view" Table for all projects
PROJTABLE[NUMBER_] - Unique Project ID
PROJTABLE[NAME] - Project name (some have the same name)
PROJTABLE[SEACHNAME] - Recipient of the completed project, this can be a employee, customer or the company itself.
'PROJTRANS' - All transactions to all projects.
PROJTRANS[EMPLOYEE] - Employee ID
PROJTRANS[NUMBER_] - Project ID
PROJTRANS[COSTTYPE] - ID for a cost type used
PROJTRANS[QTY] - quantity used on the transaction, the unit changes based on COSTTYPE.
Goal:
compare PROJTRANS[QTY] (with a filter on two specific PROJTRANS[COSTTYPE]) with EMPLOYEE[STDHRS] and show how far the employee is from or over the goal in percentage.
Maybe something like this:
| | Jan | Feb | Mar | Apr |
| Anakin Skywalker | +10,00% | -5,00% | +0,50% | -20,00% |
| Ronan Dex | +20,25% | +7,50% | +1,00% | +6,75% |
| Peter Parker | -15,00% | -17,00% | -13,75% | -5,00% |
Notes:
it might be relevent to know that the PROJTRANS table contains roughly 300.000 rows, and grows around 80-100 rows earch day.
