I have a list of accounts that are overdue and each week and there are new overdue accounts and some accounts are corrected. The goal is to have close to zero overdue accounts if possible. Therefore, I want to capture my week to week or month to month overdue accounts to see information on which employees are trending in the right direction. Unfortunately my data source only provides which accounts are overdue at a given time when the data is downloaded so when I load new data on to Qlik I lose the previous weeks totals. I know I could create a separate excel file where I manually track the overdue account totals from the previous reports and upload the data in Qlik but it would be great if this could be done by Qlik with a resident table/drop table script when the new data is loaded.
Below is the current script. I would like to keep the previous reported Contract Status (Overdue, Due, Current, Final) totals only by Organization by Business Sector so I can create a line chart.
[Raw Data]:
LOAD
[Contract ID] as Key,
[Contract Number],
[Order Number],
[Contract Status],
Date(Date#([Contract Due Date], 'MM/DD/YYYY') ) AS [Contract Due Date],
[Business Sector],
Date(Date#([Contract Completion Date], 'MM/DD/YYYY') ) AS [Contract Completion Date],
[Organization],
[DCC/CCC],
[DIV]
From [lib://AttachedFiles/ContractStatus.xlsx]
(ooxml, embedded labels, table is [Raw Data]);
