7 Replies Latest reply: Jan 26, 2017 3:09 AM by Mikhail Barannikov RSS

    Calculate rolling 12 month in script

    Mikhail Barannikov

      Good afternoon Qlik Community,

       

      this is my very fist question here and I hope to get help from the Qlik community.

      I´d like to calculate rolling 12 months in the script in multi dimensional table. There are a lot of methods to calculate it in the front-end e.g. Rangesum + Above, but I´d like to do it in the script. You can find the example below.

      The question is how to calculate e.g. rolling 12 month for (Sup1-Cus1-Mai 2015)? So at the end we have a field with rolling 12 month with regard of dimension. There is no Above or AGGR function in the script.... and I don´t want to use the AsOfCalendar for the front-end

       

      Thank you.

       

       

      LOAD * INLINE [

          Supplier, Customer, MonthYear, Qty

          Sup1, Cus1, Jan 14, 249

          Sup1, Cus1, Feb 14, 777

          Sup1, Cus1, Mrz 14, 554

          Sup1, Cus1, Apr 14, 942

          Sup1, Cus1, Mai 14, 327

          Sup1, Cus1, Jun 14, 189

          Sup1, Cus1, Jul 14, 176

          Sup1, Cus1, Aug 14, 698

          Sup1, Cus1, Sep 14, 921

          Sup1, Cus1, Okt 14, 221

          Sup1, Cus1, Nov 14, 196

          Sup1, Cus1, Dez 14, 705

          Sup1, Cus1, Jan 15, 998

          Sup1, Cus1, Feb 15, 932

          Sup1, Cus1, Mrz 15, 385

          Sup1, Cus1, Apr 15, 641

          Sup1, Cus1, Mai 15, 339

          Sup1, Cus1, Jun 15, 755

          Sup1, Cus1, Jul 15, 859

          Sup1, Cus1, Aug 15, 204

          Sup1, Cus1, Sep 15, 752

          Sup1, Cus1, Okt 15, 358

          Sup1, Cus1, Nov 15, 825

          Sup1, Cus1, Dez 15, 897

          Sup1, Cus2, Jan 14, 373

          Sup1, Cus2, Feb 14, 223

          Sup1, Cus2, Mrz 14, 380

          Sup1, Cus2, Apr 14, 865

          Sup1, Cus2, Mai 14, 635

          Sup1, Cus2, Jun 14, 637

          Sup1, Cus2, Jul 14, 367

          Sup1, Cus2, Aug 14, 396

          Sup1, Cus2, Sep 14, 661

          Sup1, Cus2, Okt 14, 520

          Sup1, Cus2, Nov 14, 804

          Sup1, Cus2, Dez 14, 158

          Sup1, Cus2, Jan 15, 938

          Sup1, Cus2, Feb 15, 510

          Sup1, Cus2, Mrz 15, 400

          Sup1, Cus2, Apr 15, 402

          Sup1, Cus2, Mai 15, 498

          Sup1, Cus2, Jun 15, 187

          Sup1, Cus2, Jul 15, 776

          Sup1, Cus2, Aug 15, 953

          Sup1, Cus2, Sep 15, 942

          Sup1, Cus2, Okt 15, 278

          Sup1, Cus2, Nov 15, 238

          Sup1, Cus2, Dez 15, 368

          Sup1, Cus3, Jan 14, 802

          Sup1, Cus3, Feb 14, 346

          Sup1, Cus3, Mrz 14, 160

          Sup1, Cus3, Apr 14, 934

          Sup1, Cus3, Mai 14, 716

          Sup1, Cus3, Jun 14, 801

          Sup1, Cus3, Jul 14, 389

          Sup1, Cus3, Aug 14, 358

          Sup1, Cus3, Sep 14, 660

          Sup1, Cus3, Okt 14, 693

          Sup1, Cus3, Nov 14, 422

          Sup1, Cus3, Dez 14, 332

          Sup1, Cus3, Jan 15, 340

          Sup1, Cus3, Feb 15, 233

          Sup1, Cus3, Mrz 15, 749

          Sup1, Cus3, Apr 15, 609

          Sup1, Cus3, Mai 15, 675

          Sup1, Cus3, Jun 15, 230

          Sup1, Cus3, Jul 15, 280

          Sup1, Cus3, Aug 15, 443

          Sup1, Cus3, Sep 15, 153

          Sup1, Cus3, Okt 15, 341

          Sup1, Cus3, Nov 15, 512

          Sup1, Cus3, Dez 15, 928

          Sup2, Cus1, Jan 14, 754

          Sup2, Cus1, Feb 14, 637

          Sup2, Cus1, Mrz 14, 568

          Sup2, Cus1, Apr 14, 877

          Sup2, Cus1, Mai 14, 954

          Sup2, Cus1, Jun 14, 598

          Sup2, Cus1, Jul 14, 948

          Sup2, Cus1, Aug 14, 532

          Sup2, Cus1, Sep 14, 193

          Sup2, Cus1, Okt 14, 507

          Sup2, Cus1, Nov 14, 346

          Sup2, Cus1, Dez 14, 487

          Sup2, Cus1, Jan 15, 627

          Sup2, Cus1, Feb 15, 886

          Sup2, Cus1, Mrz 15, 448

          Sup2, Cus1, Apr 15, 765

          Sup2, Cus1, Mai 15, 605

          Sup2, Cus1, Jun 15, 500

          Sup2, Cus1, Jul 15, 233

          Sup2, Cus1, Aug 15, 556

          Sup2, Cus1, Sep 15, 239

          Sup2, Cus1, Okt 15, 673

          Sup2, Cus1, Nov 15, 826

          Sup2, Cus1, Dez 15, 887

          Sup2, Cus2, Jan 14, 771

          Sup2, Cus2, Feb 14, 188

          Sup2, Cus2, Mrz 14, 949

          Sup2, Cus2, Apr 14, 418

          Sup2, Cus2, Mai 14, 731

          Sup2, Cus2, Jun 14, 703

          Sup2, Cus2, Jul 14, 461

          Sup2, Cus2, Aug 14, 570

          Sup2, Cus2, Sep 14, 315

          Sup2, Cus2, Okt 14, 341

          Sup2, Cus2, Nov 14, 521

          Sup2, Cus2, Dez 14, 240

          Sup2, Cus2, Jan 15, 267

          Sup2, Cus2, Feb 15, 522

          Sup2, Cus2, Mrz 15, 797

          Sup2, Cus2, Apr 15, 377

          Sup2, Cus2, Mai 15, 365

          Sup2, Cus2, Jun 15, 393

          Sup2, Cus2, Jul 15, 851

          Sup2, Cus2, Aug 15, 155

          Sup2, Cus2, Sep 15, 295

          Sup2, Cus2, Okt 15, 273

          Sup2, Cus2, Nov 15, 619

          Sup2, Cus2, Dez 15, 915

          Sup2, Cus3, Jan 14, 731

          Sup2, Cus3, Feb 14, 891

          Sup2, Cus3, Mrz 14, 796

          Sup2, Cus3, Apr 14, 203

          Sup2, Cus3, Mai 14, 555

          Sup2, Cus3, Jun 14, 681

          Sup2, Cus3, Jul 14, 556

          Sup2, Cus3, Aug 14, 795

          Sup2, Cus3, Sep 14, 800

          Sup2, Cus3, Okt 14, 676

          Sup2, Cus3, Nov 14, 101

          Sup2, Cus3, Dez 14, 477

          Sup2, Cus3, Jan 15, 123

          Sup2, Cus3, Feb 15, 751

          Sup2, Cus3, Mrz 15, 895

          Sup2, Cus3, Apr 15, 602

          Sup2, Cus3, Mai 15, 552

          Sup2, Cus3, Jun 15, 487

          Sup2, Cus3, Jul 15, 549

          Sup2, Cus3, Aug 15, 477

          Sup2, Cus3, Sep 15, 398

          Sup2, Cus3, Okt 15, 433

          Sup2, Cus3, Nov 15, 220

          Sup2, Cus3, Dez 15, 619

      ];

        • Re: Calculate rolling 12 month in script
          Massimo Grossi

          I think you can use the as-of also in the script as a temporary table

           

          Look at the attachment

            • Re: Calculate rolling 12 month in script
              Mikhail Barannikov

              thank you very much, maxgro. I just noticed that around the boundary values, it doesn´t calculate rolling 2 months...

              e.g. if you select Sup 1 Cus 2 and 15/12/2017 as Date.

              and e.g. if you select Sup 1 Cus 2 and 15/03/2017 as Data. Please see below the 15/03/2017 selection

              15_03_2017_data_selected.PNG

               

              Probably I need to modify the Final table. And another issue, I have about 15Mio Records... so to explode with AsOf - Concept will be a nice challenge

            • Re: Calculate rolling 12 month in script
              Handry Orozco

              Hi Mikhail


              could you give an example, of the expected result ?


              Thanks

                • Re: Calculate rolling 12 month in script
                  Mikhail Barannikov

                  Hi Handry,

                   

                  basically the result is like

                   

                  for e.g. (Sup1 Cus1 May 15) is the sum of quantity from  (Sup1 Cus1 Jun 14) to (Sup1 Cus1 May 15)

                  e.g. (Sup1 Cus1 Jun 15) is the sum of quantity from  (Sup1 Cus1 Jul 14) to (Sup1 Cus1 Jun 15)

                  but once the dimension is changing the rolling 12 months should start again

                  e.g. Dimension Cus is changing  (Sup1 Cus2 Jun 15) is the sum of quantity from  (Sup1 Cus2 Jul 14) to (Sup1 Cus2 Jun 15)

                  or e.g. Dimension Sup is changing (Sup2 Cus1 Jun 15) is the sum of quantity from  (Sup2 Cus1 Jul 14) to (Sup2 Cus1 Jun 15)

                   

                  I thought about to concatenate Sup - Dimension and Cus - Dimension and treat them as one dimension to reduce complexity... but the problem with rolling and changing dimension retains

                   

                  thank you.

                • Re: Calculate rolling 12 month in script
                  Handry Orozco

                  Hi Mikhail

                   

                  Try this

                   

                  Firts Config this variable in main

                   

                  SET DateFormat='YYYYMMDD';

                  SET TimestampFormat='YYYYMMDD h:mm:ss[.fff] TT';

                  SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

                   

                  Table1:

                  LOAD * INLINE [

                      Supplier, Customer, MonthYear, Qty

                      Sup1, Cus1, Jan 14, 249

                      Sup1, Cus1, Feb 14, 777

                      Sup1, Cus1, Mrz 14, 554

                      Sup1, Cus1, Apr 14, 942

                      Sup1, Cus1, Mai 14, 327

                      Sup1, Cus1, Jun 14, 189

                      Sup1, Cus1, Jul 14, 176

                      Sup1, Cus1, Aug 14, 698

                      Sup1, Cus1, Sep 14, 921

                      Sup1, Cus1, Okt 14, 221

                      Sup1, Cus1, Nov 14, 196

                      Sup1, Cus1, Dez 14, 705

                      Sup1, Cus1, Jan 15, 998

                      Sup1, Cus1, Feb 15, 932

                      Sup1, Cus1, Mrz 15, 385

                      Sup1, Cus1, Apr 15, 641

                      Sup1, Cus1, Mai 15, 339

                      Sup1, Cus1, Jun 15, 755

                      Sup1, Cus1, Jul 15, 859

                      Sup1, Cus1, Aug 15, 204

                      Sup1, Cus1, Sep 15, 752

                      Sup1, Cus1, Okt 15, 358

                      Sup1, Cus1, Nov 15, 825

                      Sup1, Cus1, Dez 15, 897

                      Sup1, Cus2, Jan 14, 373

                      Sup1, Cus2, Feb 14, 223

                      Sup1, Cus2, Mrz 14, 380

                      Sup1, Cus2, Apr 14, 865

                      Sup1, Cus2, Mai 14, 635

                      Sup1, Cus2, Jun 14, 637

                      Sup1, Cus2, Jul 14, 367

                      Sup1, Cus2, Aug 14, 396

                      Sup1, Cus2, Sep 14, 661

                      Sup1, Cus2, Okt 14, 520

                      Sup1, Cus2, Nov 14, 804

                      Sup1, Cus2, Dez 14, 158

                      Sup1, Cus2, Jan 15, 938

                      Sup1, Cus2, Feb 15, 510

                      Sup1, Cus2, Mrz 15, 400

                      Sup1, Cus2, Apr 15, 402

                      Sup1, Cus2, Mai 15, 498

                      Sup1, Cus2, Jun 15, 187

                      Sup1, Cus2, Jul 15, 776

                      Sup1, Cus2, Aug 15, 953

                      Sup1, Cus2, Sep 15, 942

                      Sup1, Cus2, Okt 15, 278

                      Sup1, Cus2, Nov 15, 238

                      Sup1, Cus2, Dez 15, 368

                      Sup1, Cus3, Jan 14, 802

                      Sup1, Cus3, Feb 14, 346

                      Sup1, Cus3, Mrz 14, 160

                      Sup1, Cus3, Apr 14, 934

                      Sup1, Cus3, Mai 14, 716

                      Sup1, Cus3, Jun 14, 801

                      Sup1, Cus3, Jul 14, 389

                      Sup1, Cus3, Aug 14, 358

                      Sup1, Cus3, Sep 14, 660

                      Sup1, Cus3, Okt 14, 693

                      Sup1, Cus3, Nov 14, 422

                      Sup1, Cus3, Dez 14, 332

                      Sup1, Cus3, Jan 15, 340

                      Sup1, Cus3, Feb 15, 233

                      Sup1, Cus3, Mrz 15, 749

                      Sup1, Cus3, Apr 15, 609

                      Sup1, Cus3, Mai 15, 675

                      Sup1, Cus3, Jun 15, 230

                      Sup1, Cus3, Jul 15, 280

                      Sup1, Cus3, Aug 15, 443

                      Sup1, Cus3, Sep 15, 153

                      Sup1, Cus3, Okt 15, 341

                      Sup1, Cus3, Nov 15, 512

                      Sup1, Cus3, Dez 15, 928

                      Sup2, Cus1, Jan 14, 754

                      Sup2, Cus1, Feb 14, 637

                      Sup2, Cus1, Mrz 14, 568

                      Sup2, Cus1, Apr 14, 877

                      Sup2, Cus1, Mai 14, 954

                      Sup2, Cus1, Jun 14, 598

                      Sup2, Cus1, Jul 14, 948

                      Sup2, Cus1, Aug 14, 532

                      Sup2, Cus1, Sep 14, 193

                      Sup2, Cus1, Okt 14, 507

                      Sup2, Cus1, Nov 14, 346

                      Sup2, Cus1, Dez 14, 487

                      Sup2, Cus1, Jan 15, 627

                      Sup2, Cus1, Feb 15, 886

                      Sup2, Cus1, Mrz 15, 448

                      Sup2, Cus1, Apr 15, 765

                      Sup2, Cus1, Mai 15, 605

                      Sup2, Cus1, Jun 15, 500

                      Sup2, Cus1, Jul 15, 233

                      Sup2, Cus1, Aug 15, 556

                      Sup2, Cus1, Sep 15, 239

                      Sup2, Cus1, Okt 15, 673

                      Sup2, Cus1, Nov 15, 826

                      Sup2, Cus1, Dez 15, 887

                      Sup2, Cus2, Jan 14, 771

                      Sup2, Cus2, Feb 14, 188

                      Sup2, Cus2, Mrz 14, 949

                      Sup2, Cus2, Apr 14, 418

                      Sup2, Cus2, Mai 14, 731

                      Sup2, Cus2, Jun 14, 703

                      Sup2, Cus2, Jul 14, 461

                      Sup2, Cus2, Aug 14, 570

                      Sup2, Cus2, Sep 14, 315

                      Sup2, Cus2, Okt 14, 341

                      Sup2, Cus2, Nov 14, 521

                      Sup2, Cus2, Dez 14, 240

                      Sup2, Cus2, Jan 15, 267

                      Sup2, Cus2, Feb 15, 522

                      Sup2, Cus2, Mrz 15, 797

                      Sup2, Cus2, Apr 15, 377

                      Sup2, Cus2, Mai 15, 365

                      Sup2, Cus2, Jun 15, 393

                      Sup2, Cus2, Jul 15, 851

                      Sup2, Cus2, Aug 15, 155

                      Sup2, Cus2, Sep 15, 295

                      Sup2, Cus2, Okt 15, 273

                      Sup2, Cus2, Nov 15, 619

                      Sup2, Cus2, Dez 15, 915

                      Sup2, Cus3, Jan 14, 731

                      Sup2, Cus3, Feb 14, 891

                      Sup2, Cus3, Mrz 14, 796

                      Sup2, Cus3, Apr 14, 203

                      Sup2, Cus3, Mai 14, 555

                      Sup2, Cus3, Jun 14, 681

                      Sup2, Cus3, Jul 14, 556

                      Sup2, Cus3, Aug 14, 795

                      Sup2, Cus3, Sep 14, 800

                      Sup2, Cus3, Okt 14, 676

                      Sup2, Cus3, Nov 14, 101

                      Sup2, Cus3, Dez 14, 477

                      Sup2, Cus3, Jan 15, 123

                      Sup2, Cus3, Feb 15, 751

                      Sup2, Cus3, Mrz 15, 895

                      Sup2, Cus3, Apr 15, 602

                      Sup2, Cus3, Mai 15, 552

                      Sup2, Cus3, Jun 15, 487

                      Sup2, Cus3, Jul 15, 549

                      Sup2, Cus3, Aug 15, 477

                      Sup2, Cus3, Sep 15, 398

                      Sup2, Cus3, Okt 15, 433

                      Sup2, Cus3, Nov 15, 220

                      Sup2, Cus3, Dez 15, 619

                  ];

                   

                  //*******************************

                  //*******************************

                  UNQUALIFY*;

                  NoConcatenate


                  Table2:

                       LOAD  Supplier,     

                                   Customer,   

                                  Date(Date#(MonthYear&' 01', 'MMM YY DD'),'YYYYMMDD')  AS MonthYear, 

                                    Qty

                       Resident  Table1;

                       Drop Table  Table1;


                  //*******************************

                  //*******************************

                  UNQUALIFY*;

                  NoConcatenate


                  Table3:

                       LOAD   Supplier,     

                  Customer,    

                  MonthYear,  

                    Date(AddMonths(MonthYear,-12),'YYYYMMDD') AS MonthLast,    

                  Qty

                  Resident Table2

                  Order by Supplier,Customer,MonthYear;

                  Drop Table Table2;

                  //*******************************

                  //*******************************

                  LET vCant = NoOfRows('Table3')-1;

                  TRACE Num Of Record $(vCant);


                  FOR i=0 TO $(vCant)

                  LET Supplier = Peek('Supplier',$(i),'Table3');

                  LET Customer = Peek('Customer',$(i),'Table3');

                  LET MonthYear = Peek('MonthYear',$(i),'Table3');

                  LET MonthLast = Peek('MonthLast',$(i),'Table3');

                  LET Qty = Peek('Qty',$(i),'Table3');

                  TRACE $(Supplier)-$(Customer)-$(MonthYear)-$(MonthLast)-$(Qty);


                  Dummy:

                       LOAD  Supplier   AS SupplierN, 

                                     Customer   AS CustomerN, 

                                   '$(MonthYear)'  AS MonthYear, 

                                  '$(Qty)'   AS Qty, 

                                  Sum(Qty)   AS YQty

                       Resident Table3

                       Where Supplier = '$(Supplier)'

                       And Customer = '$(Customer)'

                       And  (MonthYear <= Date#('$(MonthYear)','YYYYMMDD')

                       And MonthYear > Date#('$(MonthLast)','YYYYMMDD'))

                      Group By Supplier,Customer;


                  NEXT i;

                      DROP Table Table3;