I have the attached script to create Data_2013 loaded from 3 excel files. Each file contains the time worked in different periods (months) by each employee in different Tasks types (WK01, WK02, WK03 and WK04). The files also contains [Nominal Mwd], which is the nominal time for each employee in each period. Both the absolute and percentage (%) values for each Task type are available in the files. Percentages are related to [Nominal Mwd].
The script creates the field Period, by defining the period corresponding to each month ('2013P01', '2013P02' and '2013P03'). Also some Quartils are defined by an IF condition, depending on the [WK01%+WK02%] values for each employee, compared with certain threshold levels.
I need to create an aditional calculation to contain the sum of the [WK01%+WK02%] values for the 3 periods divided by the sum of the Nominal Mwd for each employee. Using the script variables, the formula should be something like this:
(Prod_01 + Prod_02 + Prod_03) / (Nominal_P01 + Nominal_P02 + Nominal_P03) For each employee
So if an employee worked 2, 3 and 5 days respectively in each month, and total Nominal Mwd is 20 for the 3 periods, the result for this employee along the 3 months is 50%.