I am working on a dashboard that will help us plan for our data and backup storage requirements for the IT group. They want to be able to forecast how much backup storage will be required specifically based on the data retention they enter.
I am having trouble getting this to work correctly and I feel what I need is a way within Qlik script to be able to sub-total the last X rows based on a variable field.
I have a data table that provides some information about the systems being backed up. It contains the amount of storage they presently have, their rate of growth per day, and some other information regarding data compression and deduplication for backup.
|Node||Base Disk Size GB||Change Rate %||Backup Start||Compression Ratio||Daily Retention|
From this information I can start to make some calculations.
First, I create a table filling in a growth expectation for the data based on 10% change looking 365 days into the future.
For each of those days, I have created a backup counter that creates an incremental backup of the base disk with growth and apply compression. I end up with a table that looks something like this. (I made up the numbers for the example)
|Node||Base Disk||Base Disk Growth||Date||Backup Size||Retention Day||Days to Retain|
As you can see in the example, the backup size will growth as the base disk grows over time - however they only want to subtotal the amount of data that would be retained per their policy
So in the instance, I would like to subtotal in script the last X rows (using the Daily Retention)
Ultimately, I'd like to end up with a table that looks something like this
|Node||Retention Point||Size||Logic Explanation|
|A||4/6/2017||445||Sum of Last X Rows Backup Size (95.7) + Base Disk (350)|
|A||4/11/2017||449||Sum of Last X Rows Backup Size (109) + Base Disk (350)|
I know I could do this using the Peek() function, but the problem I am having is that the days to retain is going to be different per node. How can I go back X number of rows to get this rolling total within the script dynamically?