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: 
Anonymous
Not applicable

Week Number Before Certain Week

So we have an event that starts on 9/16/2018

We want to bucket a DATEFIELD by the week it falls in leading up to the event

For example, the week of 9/9/2018 (and any date that falls in that week) would = 1 because it is one week before the week of the event

We want to be able to build this all the out to 50+ weeks before event

Any suggestions?

4 Replies
sunny_talwar

Are you looking to generate these dates or are looking to flag them somehow?

Anonymous
Not applicable
Author

Just looking to assign a number to it

For instance, the number should = the week before the event date like ..

1 week before date

2 weeks before date

3 weeks before date

etc ..

sunny_talwar

Is this something you need in the script? In a chart? I am not entirely sure I understand the requirement well enough to suggest anything right now... may be sample or sample data might help understand the issue better

Anonymous
Not applicable
Author

I am trying to do it in the load script

Right now it is manual ... see below

IF(NewResponseDate >= '9/4/2016' AND NewResponseDate < '9/11/2016', 1, 0) as WeekBefore

And I have to build an IF statement with 50 lines if I want to go back 50 weeks

I think there should be an easier way, but maybe I am wrong