Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Arjunarao
Honored Contributor II

Rolling 12 months sum

Hi All,

I want to show rolling 12 months in the following format in a pivot table.

Sales of Jan-14 is to be shown sum of Sales from  Feb-13 to Jan-14.

Sales of Feb-14 is to be shown sum of Sales from  Mar-13 to Feb-14.

and so on ...

Sales of Jan-16 is to be shown sum of Sales from  Feb-15 to Jan-16.

Sales of Feb-16 is to be shown sum of Sales from  Mar-15 to Feb-16

I am able to show rolling 12 months as shown.

Capture.JPG

Please help me.

Gysbert Wassenaar

jagan mohan rao appala

MAYIL VAHANAN RAMASAMY

Celambarasan Adhimulam

Kush141087

Sunny T

swuehl

Script:

Sales:

EmpSales:

load *,  MonthEnd(date([Transaction Date],'YYYYMM')) as Period1, MonthName([Transaction Date]) as Month;

load *,MonthEnd(date#(MonthName&'-'&Year,'MMM-YYYY')) as [Transaction Date];

Load * inline

[Year,MonthName,Product,Sales

2014,Jan,E1,1

2014,Feb,E1,2

2014,Mar,E1,6

2014,Apr,E1,7

2014,May,E1,8

2014,Jun,E1,9

2014,Jul,E1,10

2014,Aug,E1,1

2014,Sep,E1,2

2014,Oct,E1,3

2014,Nov,E1,4

2014,Dec,E1,5

2015,Jan,E1,6

2015,Feb,E1,7

2015,Mar,E1,8

2015,Apr,E1,9

2015,May,E1,10

2015,Jun,E1,1

2015,Jul,E1,2

2015,Aug,E1,3

2015,Sep,E1,4

2015,Oct,E1,5

2015,Nov,E1,6

2015,Dec,E1,7

2016,Jan,E1,6

2016,Feb,E1,7

2014,Jan,E2,9

2014,Feb,E2,10

2014,Mar,E2,4

2014,Apr,E2,5

2014,May,E2,6

2014,Jun,E2,7

2014,Jul,E2,8

2014,Aug,E2,9

2014,Sep,E2,3

2014,Oct,E2,4

2014,Nov,E2,5

2014,Dec,E2,6

2015,Jan,E2,7

2015,Feb,E2,8

2015,Mar,E2,9

2015,Apr,E2,10

2015,May,E2,1

2015,Jun,E2,2

2015,Jul,E2,3

2015,Aug,E2,4

2015,Sep,E2,5

2015,Oct,E2,9

2015,Nov,E2,10

2015,Dec,E2,1

2016,Jan,E2,2

2016,Feb,E2,3];

PeriodTbl:

LOAD date(fieldvalue('Period1',recno()),'YYYYMM') as Period

AUTOGENERATE fieldvaluecount('Period1');

AsOfPeriodTable:

LOAD

Period as AsOfPeriod

,'Current' as PeriodType

,Period as Period1

,Year(Period) as Year

RESIDENT PeriodTbl;

CONCATENATE (AsOfPeriodTable)

LOAD

Period as AsOfPeriod

,'Rolling 12' as PeriodType

,date(addmonths(Period,1-iterno()),'YYYYMM') as Period1

,Year(Period) as Year

RESIDENT PeriodTbl

WHILE iterno() <= 12;

DROP TABLE PeriodTbl;

1 Solution

Accepted Solutions
MVP
MVP

Re: Rolling 12 months sum

Hi,

Try this script

EmpSales:

LOAD *,

  MonthEnd(Date([Transaction Date],'YYYYMM')) as Period1,

  MonthName([Transaction Date]) as Month;

LOAD *,

  MonthEnd(Date#(MonthName&'-'&Year,'MMM-YYYY')) as [Transaction Date];

LOAD * Inline [

Year,MonthName,Product,Sales

2014,Jan,E1,1

2014,Feb,E1,2

2014,Mar,E1,6

2014,Apr,E1,7

2014,May,E1,8

2014,Jun,E1,9

2014,Jul,E1,10

2014,Aug,E1,1

2014,Sep,E1,2

2014,Oct,E1,3

2014,Nov,E1,4

2014,Dec,E1,5

2015,Jan,E1,6

2015,Feb,E1,7

2015,Mar,E1,8

2015,Apr,E1,9

2015,May,E1,10

2015,Jun,E1,1

2015,Jul,E1,2

2015,Aug,E1,3

2015,Sep,E1,4

2015,Oct,E1,5

2015,Nov,E1,6

2015,Dec,E1,7

2016,Jan,E1,6

2016,Feb,E1,7

2014,Jan,E2,9

2014,Feb,E2,10

2014,Mar,E2,4

2014,Apr,E2,5

2014,May,E2,6

2014,Jun,E2,7

2014,Jul,E2,8

2014,Aug,E2,9

2014,Sep,E2,3

2014,Oct,E2,4

2014,Nov,E2,5

2014,Dec,E2,6

2015,Jan,E2,7

2015,Feb,E2,8

2015,Mar,E2,9

2015,Apr,E2,10

2015,May,E2,1

2015,Jun,E2,2

2015,Jul,E2,3

2015,Aug,E2,4

2015,Sep,E2,5

2015,Oct,E2,9

2015,Nov,E2,10

2015,Dec,E2,1

2016,Jan,E2,2

2016,Feb,E2,3

];

PeriodTbl:

LOAD Date(FieldValue('Period1',RecNo()),'YYYYMM') as Period

AutoGenerate FieldValueCount('Period1');

AsOfPeriodTable:

LOAD Period as AsOfPeriod,

  Year(Period) AS AsOfYear,

  Month(Period) AS AsOfMonth,

  'Current' as PeriodType,

  Period as Period1

// Year(Period) as Year

Resident PeriodTbl;

Concatenate (AsOfPeriodTable)

LOAD Period as AsOfPeriod,

Year(Period) AS AsOfYear,

  Month(Period) AS AsOfMonth,

  'Rolling 12' as PeriodType,

  Date(MonthEnd(AddMonths(Period, 1-IterNo())), 'YYYYMM') as Period1

// Year(Period) as Year

Resident PeriodTbl

While IterNo() <= 12;

DROP Table PeriodTbl;

Regards,

Jagan.

6 Replies

Re: Rolling 12 months sum

May be this:

EmpSales:

LOAD *,

  MonthEnd(Date([Transaction Date],'YYYYMM')) as Period1,

  MonthName([Transaction Date]) as Month;

LOAD *,

  MonthEnd(Date#(MonthName&'-'&Year,'MMM-YYYY')) as [Transaction Date];

LOAD * Inline [

Year,MonthName,Product,Sales

2014,Jan,E1,1

2014,Feb,E1,2

2014,Mar,E1,6

2014,Apr,E1,7

2014,May,E1,8

2014,Jun,E1,9

2014,Jul,E1,10

2014,Aug,E1,1

2014,Sep,E1,2

2014,Oct,E1,3

2014,Nov,E1,4

2014,Dec,E1,5

2015,Jan,E1,6

2015,Feb,E1,7

2015,Mar,E1,8

2015,Apr,E1,9

2015,May,E1,10

2015,Jun,E1,1

2015,Jul,E1,2

2015,Aug,E1,3

2015,Sep,E1,4

2015,Oct,E1,5

2015,Nov,E1,6

2015,Dec,E1,7

2016,Jan,E1,6

2016,Feb,E1,7

2014,Jan,E2,9

2014,Feb,E2,10

2014,Mar,E2,4

2014,Apr,E2,5

2014,May,E2,6

2014,Jun,E2,7

2014,Jul,E2,8

2014,Aug,E2,9

2014,Sep,E2,3

2014,Oct,E2,4

2014,Nov,E2,5

2014,Dec,E2,6

2015,Jan,E2,7

2015,Feb,E2,8

2015,Mar,E2,9

2015,Apr,E2,10

2015,May,E2,1

2015,Jun,E2,2

2015,Jul,E2,3

2015,Aug,E2,4

2015,Sep,E2,5

2015,Oct,E2,9

2015,Nov,E2,10

2015,Dec,E2,1

2016,Jan,E2,2

2016,Feb,E2,3

];

PeriodTbl:

LOAD Date(FieldValue('Period1',RecNo()),'YYYYMM') as Period

AutoGenerate FieldValueCount('Period1');

AsOfPeriodTable:

LOAD Period as AsOfPeriod,

  'Current' as PeriodType,

  Period as Period1

// Year(Period) as Year

Resident PeriodTbl;

Concatenate (AsOfPeriodTable)

LOAD Period as AsOfPeriod,

  'Rolling 12' as PeriodType,

  Date(MonthEnd(AddMonths(Period, 1-IterNo())), 'YYYYMM') as Period1

// Year(Period) as Year

Resident PeriodTbl

While IterNo() <= 12;

DROP Table PeriodTbl;


Capture.PNG

Arjunarao
Honored Contributor II

Re: Rolling 12 months sum

Hi sunindia

Thank You for solution. All the data is matching for 12 months rolling.

But when I select Year and Month, it is showing Current Sales.

Please advise.

MVP
MVP

Re: Rolling 12 months sum

Hi,

Try this script

EmpSales:

LOAD *,

  MonthEnd(Date([Transaction Date],'YYYYMM')) as Period1,

  MonthName([Transaction Date]) as Month;

LOAD *,

  MonthEnd(Date#(MonthName&'-'&Year,'MMM-YYYY')) as [Transaction Date];

LOAD * Inline [

Year,MonthName,Product,Sales

2014,Jan,E1,1

2014,Feb,E1,2

2014,Mar,E1,6

2014,Apr,E1,7

2014,May,E1,8

2014,Jun,E1,9

2014,Jul,E1,10

2014,Aug,E1,1

2014,Sep,E1,2

2014,Oct,E1,3

2014,Nov,E1,4

2014,Dec,E1,5

2015,Jan,E1,6

2015,Feb,E1,7

2015,Mar,E1,8

2015,Apr,E1,9

2015,May,E1,10

2015,Jun,E1,1

2015,Jul,E1,2

2015,Aug,E1,3

2015,Sep,E1,4

2015,Oct,E1,5

2015,Nov,E1,6

2015,Dec,E1,7

2016,Jan,E1,6

2016,Feb,E1,7

2014,Jan,E2,9

2014,Feb,E2,10

2014,Mar,E2,4

2014,Apr,E2,5

2014,May,E2,6

2014,Jun,E2,7

2014,Jul,E2,8

2014,Aug,E2,9

2014,Sep,E2,3

2014,Oct,E2,4

2014,Nov,E2,5

2014,Dec,E2,6

2015,Jan,E2,7

2015,Feb,E2,8

2015,Mar,E2,9

2015,Apr,E2,10

2015,May,E2,1

2015,Jun,E2,2

2015,Jul,E2,3

2015,Aug,E2,4

2015,Sep,E2,5

2015,Oct,E2,9

2015,Nov,E2,10

2015,Dec,E2,1

2016,Jan,E2,2

2016,Feb,E2,3

];

PeriodTbl:

LOAD Date(FieldValue('Period1',RecNo()),'YYYYMM') as Period

AutoGenerate FieldValueCount('Period1');

AsOfPeriodTable:

LOAD Period as AsOfPeriod,

  Year(Period) AS AsOfYear,

  Month(Period) AS AsOfMonth,

  'Current' as PeriodType,

  Period as Period1

// Year(Period) as Year

Resident PeriodTbl;

Concatenate (AsOfPeriodTable)

LOAD Period as AsOfPeriod,

Year(Period) AS AsOfYear,

  Month(Period) AS AsOfMonth,

  'Rolling 12' as PeriodType,

  Date(MonthEnd(AddMonths(Period, 1-IterNo())), 'YYYYMM') as Period1

// Year(Period) as Year

Resident PeriodTbl

While IterNo() <= 12;

DROP Table PeriodTbl;

Regards,

Jagan.

MVP
MVP

Re: Rolling 12 months sum

Forgot to mention, instead of use Year & Month filters use

AsOfYear & AsOfMonth as filters.

Regards,

Jagan.

MVP
MVP

Re: Rolling 12 months sum

PFA.

Regards,

Jagan.

sasikanth
Valued Contributor III

Re: Rolling 12 months sum

hI,

Pls find the attachment

Community Browser