Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Live Chat, June 29th 10AM ET. Bring your Qik Sense Enterprise, Client Managed questions! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitk1609
Specialist III
Specialist III

How to Create Monthly Loop which ends at today's date

Hi techies,

I have an use case where I have two dates vMinDate which is lets say 1 Jan 2018 and vMaxDate which is today's date 8 Dec 2018. I want to create a monthly loop based on 30 days duration:

let vMinDate = 1 Jan 2018

let vMaxDate  = 8 Jan 2020

Table should look like

Year     Date           MonthRunningNo 

2018   1 Jan 2018            1

2018   2 Jan 2018            1

..

..

2018   30 Jan 2018         1

2018   31 Jan 2018         2

2018   1 Feb 2018           2

....

....

2020   8 Dec 2020          X

lets say by 30 days duration 8 days left to 8 Dec 2020, so last step in loop would have 8 days for month running number.

30 days should come in one step of monthly loop. 

 

Thanks in advance

Rohit

 

 

Labels (1)
1 Reply
Lisa_P
Employee
Employee

let vMinDate = Num(Date#('1 Jan 2018', 'D MMM YYYY'));
let vMaxDate = Num(Date#('8 Jan 2020', 'D MMM YYYY'));

Dates:
Load Year(Date) as Year,
Date,
(Year(Date)-Year($(vMinDate)))*12 + Num(Month(Date)) as MonthRunningNo;
LOAD
Date( StartDate + IterNo() - 1 ) as Date
While StartDate + IterNo() - 1 <= EndDate;
LOAD * INLINE
[StartDate, EndDate
$(vMinDate), $(vMaxDate)
];