Hi
I'm creating an app based on time keeping transactions where we want to implement a check on overtime worked in a dashboard.
My transactions come in with both one and multiple transactions per day - I've created calculated fields which determines if the hours are overtime or not (based on SalaryType) and add a field for the week number (so I can run calculations based on a field, and not just the autocalendar).
And if I want to just show places where amount of overtime per week is above X value - that is fine. But I want to create a flag/posting based on the formula:
if Sum of OVerTimeHours per week minus 2.5 hours is above 10 I want a field populated (either static or on the fly) that I can filter on and only show number of instances where this happens.
In addition I want to be able to do the same per month and year - where the rules are the same. Each week 2.5 hours of overtime are removed to factoring in difference between paid overtime and what laws count as overtime.
Examples:
In the attached file employee 111 works 12 hours overtime in week 45, minus 2.5 it's reported as 9,5 hours (as seen in my table view). In week 48 he works 18 hours overtime, which is reported as 15.5 hours and should be flagged.
Also, when I have a pivot table my simple IF/Sum "if(sum(Amount)<2.5,0,sum(Amount)-2.5)" counts wrong in the places where we replace the amount of overtime with 0 to avoid negative numbers (1 hour worked overtime will count as -1.5 if we do the subtraction). How to get around that?
Øystein
