Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aishan07
Contributor II
Contributor II

Need to generate date every 14 days based on data

Hello, I have  a date column like below(It has 14 days data:

1/1/2020
1/2/2020
1/3/2020
1/4/2020
1/5/2020
1/6/2020
1/7/2020
1/8/2020
1/9/2020
1/10/2020
1/11/2020
1/12/2020
1/13/2020
1/14/2020
1/15/2020
1/16/2020
1/17/2020
1/18/2020
1/19/2020
1/20/2020
1/21/2020
1/22/2020
1/23/2020
1/24/2020
1/25/2020
1/26/2020
1/27/2020

1/28/2020

I want an output like this:

Date New_Date
1/1/2020 1/14/2020
1/2/2020 1/14/2020
1/3/2020 1/14/2020
1/4/2020 1/14/2020
1/5/2020 1/14/2020
1/6/2020 1/14/2020
1/7/2020 1/14/2020
1/8/2020 1/14/2020
1/9/2020 1/14/2020
1/10/2020 1/14/2020
1/11/2020 1/14/2020
1/12/2020 1/14/2020
1/13/2020 1/14/2020
1/14/2020 1/28/2020
1/15/2020 1/28/2020
1/16/2020 1/28/2020
1/17/2020 1/28/2020
1/18/2020 1/28/2020
1/19/2020 1/28/2020
1/20/2020 1/28/2020
1/21/2020 1/28/2020
1/22/2020 1/28/2020
1/23/2020 1/28/2020
1/24/2020 1/28/2020
1/25/2020 1/28/2020
1/26/2020 1/28/2020
1/27/2020 1/28/2020

 

Starting at 1/1/2020, i need to show same date till two weeks(14 Daya) in second row, and repeat same for rest data. So i need 14 days date in second column.

Labels (2)
1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

New_Date = Date((Num([Date]) + 13 - Mod(Num([Date]) - Num(Date#('1/1/2020')),14)),'M/D/YYYY')

View solution in original post

4 Replies
jwjackso
Specialist III
Specialist III

New_Date = Date((Num([Date]) + 13 - Mod(Num([Date]) - Num(Date#('1/1/2020')),14)),'M/D/YYYY')

aishan07
Contributor II
Contributor II
Author

Thank you. It worked.

Could you please explain this code in a bit more detail?

jwjackso
Specialist III
Specialist III

The Mod() function returns the remainder of a number1/number2.  The Mod(Num(Date) - Num(Date#('1/1/202/)),14) creates a repeating series of numbers from 0 to 13.  I take the current date and add 13 - the Mod() result  which is 0 for the base date to give me a result 14 days in the future from the base date.  I then move 1 day into the future and add 13 and - the Mod() which is now 1, the result is still 14 days in the future from the base date.  See the example below.

Untitled.png

aishan07
Contributor II
Contributor II
Author

This is impressive. Appreciate your help. 🙂