Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Pick rolling 12 months

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.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

5 Replies
sunny_talwar

In the script?

apthansh
Creator
Creator
Author

yeah..if possible in where clause..

sunny_talwar

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
Creator
Creator
Author

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

apthansh
Creator
Creator
Author

ypu are awesome.Thank you