Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

Populating Dates & Day Number between Date Ranges for Different Franchisee

Hi

I have data of different Franchisees which has different calendar start and end dates for the each calendar period.  So the day number for different franchisee may be different. 

So for example, if a start date for a Franchisee is 27 Jan 2019 and end date is 24 Feb 2019,  then

Date                              Day   

27 Jan 2019                 1

28 Jan 2019                2

29 Jan 2019                3

......

23 Feb 2019                 27

24 Feb 2019                 28

 if a start date for a Franchisee is 2 Feb 2019 and end date is 4 Mar 2019

Date                              Day   

2 Feb 2019                 1

3  Feb 2019                2

4  Feb 2019                3

........

3  Mar 2019               29

4 Mar 2019               30  

 

How do i create a loop / iteration, where i need to populate the dates as well as the day number between the start and end date per Franchisee per month.

Attached herewith is the data.

Please can you assist.

Kind regards,

Nayan

 

 

 

 

 

 

Tags (1)
Labels (1)
2 Replies
mrybalko
Contributor II

Re: Populating Dates & Day Number between Date Ranges for Different Franchisee

Hello

Try this:

tmp:
LOAD
	*,
	date(CALENDAR_START_DATE + IterNo() - 1) as Date
While CALENDAR_START_DATE + IterNo() - 1 <= CALENDAR_END_DATE;

LOAD Franchisee, 
     FinancialYear, 
     FISCAL_MONTH, 
     CALENDAR_PERIOD, 
     CALENDAR_START_DATE, 
     CALENDAR_END_DATE
FROM
MVP & Luminary
MVP & Luminary

Re: Populating Dates & Day Number between Date Ranges for Different Franchisee