Business rule: Overlap rate.
So, for clarification:
1. Please define that business rule first.
2. Include an example of two employees' data and the results you want to get.
Sorry for unclear description.
overlap rate = number of people in a particular department taking leave on particular day/total number of people in a department *100%
John: take annual leave on 23/5/2017 to 28/5/2017
Kitty: take annual leave on 25/5/2017 to 1/6/2017
Total number of people in their department= 10
so the rate should be 2/10*100% =20% on 24/5/2017
is it better to calculate the rate per day?
i know there is another approach that in a particular date range say from 23/5/2017 to 1/6/2017, there are 2 of them taking a leave so the rate should be 20%
which one is better in FileMaker?
1 of 1 people found this helpful
That's a good clarification. Thanks.
So, how about a simple ExecuteSQL statement?
(click on image below if you can't see all of it.)
Here, I've "hard-coded" the start and end dates (last two parameters on the right of the ExecuteSQL), but in your application, if this is correct, you would use fields so you could enter ad-hoc dates to find employees that fall into the desired date range.
Finally, you'd need to do a little math to get your percentage with the "2" value above, but you now have the correct count of employees who fall in the date range.
You could also do this without SQL.
Does this work? Depending on exactly what you need, you might need to use an "OR" instead of an "AND" in the expression above.
HOPE THIS HELPS.
yes. it is helpful. Thank you. I would like to ask how to calculate the total number of department. Could i have a field to calculate total number of people in a specific department. I try to use a counter field and summary field to calculate, but it is not working as not all the staff in a department take a leave. Do you have any suggestion?
Do i need to modify the query? Will it overlap with other departments.
If your department count is for the table you're in, as in not a related table, use a Summary field in FMP. If the count for department is a related table, then COUNT(Primary Key) should work fine.
i create a department field in employee table to record the department of an employee. Then i create a counter field(calculation field) If department = "HR" ; 1, and summary field for this counter field, total people in HR could be calculated.
my report is based on vacation table which is related to the employee table. When i put the summary field in the layout, it is not the total number of people in HR. What was going wrong?
The summary field on a report will show different values depending on where on the report you put it.
Drag the summary field around and you'll see what I mean.
Or, you could just create a calculated field with a simple SQL statement.
Something like ExecuteSQL("select count(*) from department"; "";"")
If you have FMPA, you could create a custom function that calculates your percentage something like this:
pct_overlap =(ExecuteSQL("select count(*) from <table> where <using date logic shown in previous posting>";"";""; "<insert date1 here>"; "<insert date 2 here>") / ExecuteSQL("select count(*) from department";"";"")) * 100
Using that custom function technique, you wouldn't even need to create a field, calculated, summary, or otherwise. Just call the function in any field (or data viewer) anywhere you needed the overlap value.
HOPE THIS HELPS.