2 Replies Latest reply: Mar 28, 2017 11:01 PM by Muñoz Héctor RSS

    Script Dynamic Sub Totals

    Joe Hutchings

      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!

        • Re: Script Dynamic Sub Totals
          Muñoz Héctor

          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