topic Re: Rolling 12 months sum in QlikView

Rolling 12 months sum

qlikviewwizard — Tue, 16 Feb 2016 02:20:03 GMT

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;

Re: Rolling 12 months sum

sunny_talwar — Tue, 16 Feb 2016 03:01:20 GMT

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;

Re: Rolling 12 months sum

qlikviewwizard — Tue, 16 Feb 2016 03:33:22 GMT

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.

Re: Rolling 12 months sum

jagan — Tue, 16 Feb 2016 04:08:11 GMT

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.

Re: Rolling 12 months sum

jagan — Tue, 16 Feb 2016 04:09:05 GMT

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

AsOfYear & AsOfMonth as filters.

Regards,

Jagan.

Re: Rolling 12 months sum

jagan — Tue, 16 Feb 2016 04:09:31 GMT

PFA.

Regards,

Jagan.

Re: Rolling 12 months sum

sasikanth — Tue, 16 Feb 2016 04:38:09 GMT

hI,

Pls find the attachment