Calculate rolling 12 month in script

[Anonymous]

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

];

[maxgro]

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

Look at the attachment

[handry_orozco]

Hi Mikhail

could you give an example, of the expected result ?

Thanks

[Anonymous]

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

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

[Anonymous]

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.

[handry_orozco · Accepted solution]

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;

[Anonymous]

thank you very much, I´ll try it tomorrow evening (at the moment I am not able to use my computer).

[Anonymous]

Hello Handry, thank you, it is exactly what I was searching for.