Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master 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
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

6 Replies
sunny_talwar

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

qlikviewwizard
Master II
Master II
Author

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.

jagan
Luminary Alumni
Luminary Alumni

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.

jagan
Luminary Alumni
Luminary Alumni

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

AsOfYear & AsOfMonth as filters.

Regards,

Jagan.

jagan
Luminary Alumni
Luminary Alumni

PFA.

Regards,

Jagan.

sasikanth
Master
Master

hI,

Pls find the attachment