Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

phase2medical
New Contributor III

Script Dynamic Sub Totals

Hello --

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.

NodeBase Disk Size GBChange Rate %Backup StartCompression RatioDaily Retention
A35010%4/1/201750%5
B1009%4/1/201748%2

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)

NodeBase DiskBase Disk GrowthDateBackup SizeRetention DayDays to Retain
A3503504/2/20171715
A3503504/3/201718.725
A3503504/4/20171935
A3503504/5/20172045
A3503504/6/20172155
A3503714/7/20172015
A3503714/8/20172125
A3503714/9/20172235
A3503714/10/201722.545
A3503714/11/201723.555
A3503944/12/20172315

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 SizeLogic Explanation
A4/6/2017445Sum of Last X Rows Backup Size (95.7) + Base Disk (350)
A4/11/2017449Sum 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?

Thanks!

2 Replies
hector_munoz_in
Valued Contributor

Re: Script Dynamic Sub Totals

Hi Joe,

I think we could solve your proeblem with the following code:

BACKUP:

LOAD Node,

     [Base Disk],

     [Base Disk Growth],

     Date,

     [Backup Size],

     [Retention Day],

     [Days to Retain],

     [Backup Size] +

     If([Retention Day] = 1,

        0,

        Peek('Retention Point Size', -1, 'BACKUP')) AS [Retention Point Size]

FROM BACKUP.xlsx (ooxml, embedded labels, table is Hoja1);

NOTE: there is a strong precondition in the above code all the data in BAKUP table MUST be previously ordered by Node ASC, and Date ASC. If is not ordered so, the peek function will show an unpredictable result.

What we do in [Retention Point Size] field generation is:

  • Always sum the [Backup Size] field value.
  • Look at the current record value in field [Retention Day].
  • If is the first day of the sequence to retain I donot sum anything as I donot want to accumulate value.
  • Else, I take the value of the precedent record in the [Retention Point SIze] field as it contains the accumulated value.

Does not matter if nodes have different days to retain even if a node change its; only matter that the origin table is ordered by node and date.

The result could be:

29-03-2017 4-59-11.png

I attach you a sample. Hope it serves!

Regards,

H

hector_munoz_in
Valued Contributor

Re: Script Dynamic Sub Totals

I attach the excel file here...

Community Browser