{"text_format":"3","workbook":"xlsx","n_sheets":2,"sheets":["Rota Metric Generator","Instructions"],"not_rendered":{"macros":1},"read":"whole","date_system":"1900"}

{"sheet":"Rota Metric Generator","index":0,"n_rows":16,"n_cols":11,"rows_kept":16,"merged":[[0,0,0,5],[1,6,1,10],[5,0,5,1],[7,0,7,1],[8,0,8,1],[9,0,9,1],[10,0,10,1],[11,0,11,1],[12,0,12,1],[15,0,16,1]]}
["Fill Green Cells from Left to Right and then press Button to create Rota Metrics File"]
["Start date of the financial calendar that was loaded into customer's Analytics portal","End date of the financial calendar that was loaded into customer's Analytics portal","Run report Revenue Transactions by Unit ID in Analytics to get all relevant Unit IDs",null,null,"Customer Name will be inserted into the RM filenames that are generated","Rota Metrics Headers"]
["Fincal Start Date","Fincal End Date","Accounting System Code","LFL Start Date","LFL End Date","Customer Name","Metric Code","Location Code","Transaction Type ID","Transaction Value","Transaction Date"]
["2020-03-12","2020-04-19",123,"2020-03-12","2020-04-12","The Customer"]
[]
["Populate all dates in \"DD/MM/YYYY\" format"]
[]
["Rota Metrics files uploaded to WFM are limited to 20,000 rows"]
["One site for one year of rota metrics flags will generate 365 rows"]
["(or 36,500 rows for 10 sites for 10 years)"]
["If you generate a Rota Metric file exceeding 20,000 rows,"]
["the output will be split into several Rota Metric files "]
["Each file will need to be dropped onto the FTP with a 15 minute interval between each drop"]
[]
[]
["For help on how to populate see \"Instructions\" worksheet"]

{"sheet":"Instructions","index":1,"n_rows":26,"n_cols":7,"rows_kept":26,"merged":[[10,0,10,5],[11,0,11,5],[12,0,12,5],[13,0,13,5],[14,0,14,5],[15,0,15,5],[17,0,17,5],[18,0,18,5],[19,0,19,5],[21,0,21,5],[22,0,22,5],[23,0,23,5],[24,0,24,5],[25,0,25,5]]}
["Fincal Start Date","Fincal End Date","Accounting System Code","LFL Start Date","LFL End Date","Customer Name"]
["2018-10-01","2025-09-28",1234,"2018-10-01","2025-09-28","customerABC","The contents of this cell will be used in naming the file(s) generated"]
[null,null,5678,"2019-03-04","2020-05-04",null,"e.g. if you enter \"customerABC\" the csv file(s) will be named:"]
[null,null,5678,"2020-05-05","2025-09-28",null,"Metrics_LFL_customerABC_Part1_created_11_Jun_20.csv"]
[]
[]
[]
[]
[]
[]
["This tool is used to generate Rota Metrics files that can be uploaded to Workforce Management"]
["The Rota Metrics files will contain 1 row per location per day - indicating whether it is a like for like location on that day (1 flag) or not a like for like location on that day (0 flag)"]
["Any dates between the Fincal Start Date and the location's LFL Start Date will have a 0 flag"]
["Any dates between the location's LFL Start Date and the location's LFL End Date will have a 1 flag"]
["Any dates between the location's LFLEnd Date and the Fincal End Date will have a 0 flag"]
["If a location should have multiple LFL Start and End dates - e.g. for temporary closures, then multiple rows should be populated for that location (as per example 2)"]
[]
["Example 1 - Location with Accounting System Code 1234"]
["Was a like for like location since before the Financial calendar start date.  However, as Analytics does not store data beyond the Financial calendar date range, the LFL Start date cannot be earlier than the Fincal Start date, hence LFL Start date is the same as the Fincal Start date"]
["It remains (or is expected to remain) a like for like location up until the end of the Financial Calendar that is loaded in Fourth Analytics, hence LFL End date is the same as the Fincal End date"]
[]
["Example 2 - Location with Accounting System Code 5678 "]
["Became a like for like location on 4/3/2019 and remains a like for like location up until 4/5/2020 when it undergoes a month long closure for refurbishment before opening again on 5/5/2020"]
["In this scenarion we need 2 rows for location 5678:"]
["first row runs from 4/3/19 (LFL Start) to 4/5/20 (LFL End i.e.until closure)"]
["second row starts on reopening date (LFL start = 5/5/20) and runs until the end of the Fincal Calendar or for however long the location is expected to remain a like for like lcoation"]