topic Re: Pick rolling 12 months in QlikView

Pick rolling 12 months

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have data as attached..I want this to pick Rolling 12 months..I want to pick rolling 12 month data..

Ex: From the below screenshot I want to pick data from 2017 - Mar to 2018 -Feb.

Next month when 2018 - Mar gets added I want to show data from 2017 - Apr to 2018 - Mar.

sunny_talwar — Tue, 10 Apr 2018 19:12:35 GMT

In the script?

apthansh — Tue, 10 Apr 2018 19:15:15 GMT

yeah..if possible in where clause..

sunny_talwar — Tue, 10 Apr 2018 19:20:47 GMT

One possible way

Table:

LOAD Year,

[Posting Period],

year(MakeDate(Year)) as ExpenseYear,

month(MakeDate(2000,[Posting Period])) as ExpenseMonth,

Date(MakeDate(Year, [Posting Period]), 'YYYY-MMM') as ExpenseMonthYear

// year(MakeDate(Year)) & '-'& month(MakeDate(2000,[Posting Period])) as ExpenseMonthYear

FROM

[ExpenseS.txt.xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (Table)

LOAD MonthStart(ExpenseMonthYear, IterNo()) as ExpenseMonthYear

While IterNo() <= 12;

LOAD MonthStart(Max(ExpenseMonthYear), -12) as ExpenseMonthYear

Resident Table;

apthansh — Tue, 10 Apr 2018 19:21:14 GMT

if not just filtering in the fields itself is ok too..

apthansh — Wed, 11 Apr 2018 02:03:22 GMT

ypu are awesome.Thank you