Generate the fortnight (two weeks) time intervals for a given date range

[andymanu]

Hi All,

I need to generate the two weeks time periods for a given date range.

For an example, assume the date range is 25/06/2018 - 5/08/2018, the resulted output should be;

25/06/2018 - 8/07/2018

9/07/2018 - 22/07/2018

23/07/2018 - 5/08/2018.

I found the below code to generate the in between dates which is working fine.

"Vacation Start Date" + IterNo() -1 as "Vacation Date"
Resident Vacation_Dates_Table
WHILE "Vacation Start Date" + IterNo() -1 <= "Vacation End Date";

Need a similar one to generate the above said intervals and need to group them (ex - 25/06/2018 - 8/07/2018) as shown above.

Appreciate you valuable feedback in advance.

Kind regards,

Andy

[HirisH_V7]

Hi Check the below code for generating the weekstart and enddates for the required dates.

Date:
LOAD * INLINE [
    DateFrom, DateTo
    25/06/2018, 5/08/2018
];

Let vMin=Num(Date#(Peek('DateFrom'),'DD/MM/YYYY'));
Let vMax=Num(Date#(Peek('DateTo'),'DD/MM/YYYY'));

Drop table Date;

Temp:
Load 
Date($(vMin) + RowNo() - 1) AS Vacation_Date
AutoGenerate (1)
WHILE $(vMin) + IterNo() -1 <= $(vMax);

Data:
Load *,
WeekStart(Vacation_Date) & 'To ' & WeekEnd(Vacation_Date)  as  Week_Range

Resident Temp;

DROP table Temp;

Do the mods required for your requirement accordingly. Let me know if any.

PFA QVW.

HTH,

Hirish

[andymanu]

Hi Hirish,

Thanks for the valuable reply.

FirstWeekDay=6

However, after changing the default setting to 7 (instead of 6), I was able to get the expected results.

1. Is there any other way to overcome the above said without changing the default main page setting?

2. In addition to above,  I want to group the weeks in to pairs (cos generally, the salaries are paid fortnightly and it is based on the hours worked for the respective fortnight).

Could you please help me with grouping the individual Week_Range in to pairs and the final result for the above provided date range should be,

25/06/2018 To 8/07/2018

9/07/2018 To 22/07/2018

23/07/2018 To 5/08/2018

Thank you in advance.

Kind regards,

Andy

It is working but had to change one of the default setting of the "Main" page . After changing the  "SET FirstWeekDay=7;"

only it gave the correct results. Is there any other way I could 

[andymanu]

Please disregard the last few lines starting with " It is working but ...." cos I have forgotten to delete that bit

[HirisH_V7 · Accepted solution]

Please Find attached updated QVW. 

Pl Check below code, hope it answers all your queries.

Date:
LOAD * INLINE [
    DateFrom, DateTo
    25/06/2018, 5/08/2018
];

Let vMin=Num(Date#(Peek('DateFrom'),'DD/MM/YYYY'));
Let vMax=Num(Date#(Peek('DateTo'),'DD/MM/YYYY'));

Drop table Date;

Temp:
Load 
Date($(vMin) + RowNo() - 1) AS Vacation_Date
AutoGenerate (1)
WHILE $(vMin) + IterNo() -1 <= $(vMax);

Data:
Load *,
If(Mod(Week(Vacation_Date,0,7),2)=0,
WeekStart(Vacation_Date,0,7) & ' To ' & WeekEnd(Vacation_Date,1,7),
WeekStart(Vacation_Date,-1,7) & ' To ' & WeekEnd(Vacation_Date,0,7)) as Week_Range


Resident Temp;

DROP table Temp;

HTH,

Hirish

[andymanu]

HI Hirish,
Thank you very much Hirish.
Yes, its working now as I expected. Thanks a lot, learned something new about grouping weeks also.
Really great.......
Your answer was clear and prompt.
Kind regards,
Andy

[HirisH_V7]

No problem!
Enjoy Qliking.. 🙂