Facility Management Services - Billable Days Vs Agreement Coverage Case Study

 

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 ~

 

 

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

 

 

 

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