How to store historical data

Malik A.

We need an Overdue POs graph in OAC/FDI:

X-axis: Fiscal Date (daily).

Y-axis: Total number/quantity of overdue POs as of that date.

See image.

For example:

On 29-Sep → show how many POs were overdue as of 29-Sep.

On 30-Sep → show how many POs were overdue as of 30-Sep.

On 01-Oct → show how many POs were overdue as of 01-Oct.

They want it to work like a daily snapshot — whatever POs were overdue yesterday should be shown against that date, same for today, and so on.

We Created it using subject area and calculated overdue based on formula. But fiscal date is triggering only for PO creation date and the bar will decrease once POs are received —> So if something is received on evening of 30th Sep and once data refreshes, on 1 Oct, the bar of 30th Sep also decreases. Whereas as per requirement, 30th Bar should stay as it, changes should only be in 1st Oct data. So, it's live data and we need snapshot of historical data as well.

If we want to avoid those we have to create a custom table in adw that stores daily snapshot data which is overdue and fiscal date. How can we achieve this?

Please let me know your thoughts on this