Rolling 12 months sum

[qlikviewwizard]

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.

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;

[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;

[qlikviewwizard]

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 · Accepted solution]

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]

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

AsOfYear & AsOfMonth as filters.

Regards,

Jagan.

[jagan]

PFA.

Regards,

Jagan.

[sasikanth]

hI,

Pls find the attachment