topic Re: Calculate rolling 12 month in script in QlikView

Calculate rolling 12 month in script

Anonymous — Mon, 16 Jan 2017 18:12:34 GMT

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

maxgro — Mon, 16 Jan 2017 19:02:53 GMT

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

handry_orozco — Mon, 16 Jan 2017 19:50:03 GMT

Hi Mikhail

could you give an example, of the expected result ?

Thanks

Re: Calculate rolling 12 month in script

Anonymous — Mon, 16 Jan 2017 20:45:27 GMT

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

Re: Calculate rolling 12 month in script

Anonymous — Mon, 16 Jan 2017 20:55:56 GMT

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 — Tue, 17 Jan 2017 19:49:19 GMT

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;

Re: Calculate rolling 12 month in script

Anonymous — Wed, 18 Jan 2017 07:04:36 GMT

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

Re: Calculate rolling 12 month in script

Anonymous — Thu, 26 Jan 2017 08:09:52 GMT

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