Facility Management Services - Billable Days Vs Agreement Coverage Case Study

1 post / 0 new
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
Facility Management Services - Billable Days Vs Agreement Coverage Case Study

Hello Group Members,

 
Say you are performing a data analytic review for a Facility Management Company and your task is to ensure the Company has billed each of their Clients for the right number of days where Facility Services have been provided each month per Location. The data schema for the file being reviewed in IDEA looks as below ~
 
Client Code|Location Code|Agreement Start Date|Agreement End Date|Month Start Date|Month End Date| No of Days|Rate|Amount|Taxes|Total
 
Sample entries as per the schema above are as such ~
 
Client Code|Location Code|Agreement Start Date|Agreement End Date|Month Start Date|Month End Date| No of Days|Rate|Amount|Taxes|Total
 
ABC|DEL|05.01.2019|04.03.2019|01.02.2019|28.02.2019|28|50|1400|252|1652
 
PQR|MUM|20.01.2019|19.02.2019|01.02.2019|28.02.2019|28|50|1400|252|1652 - Excess Billing
 
XYZ|PNQ|01.01.2019|31.03.2019|01.02.2019|28.02.2019|10|50|500|90|590 - Short Billing
 
Now our task is to identify ~
 
(a) Cases where the Agreement Start Date and End Date are before and after the Month Start and End Date respectively - hence - the full number of days for the month of Feb 2019 (sample entries above) to be billed.
 
(b) Cases where the Agreement Start Date and End Date are both within the Month Start or End Date respectively - hence - prorata number of days to be billed only as per Agreement End Date and Start Date only disregarding the Month Start and End Date.
 
The above can be achieved by performing a Direct Extraction in IDEA with the criteria ~
 
@if(Agreement Start Date < Month Start Date .AND. Agreement End Date > Month End Date, @age(Month End Date, Month Start Date), @age(Agreement End Date, Agreement Start Date))
 
Best Regards
 
Group Admin Team