Populating Dates & Day Number between Date Ranges for Different Franchisee

[nayanqlik]

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

[mrybalko]

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

[Gysbert_Wassenaar]

See this blog post: https://community.qlik.com/t5/Qlik-Design-Blog/Creating-Reference-Dates-for-Intervals/ba-p/1463944