topic Week Number Before Certain Week in QlikView

Week Number Before Certain Week

Anonymous — Fri, 10 Aug 2018 15:56:02 GMT

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?

Re: Week Number Before Certain Week

sunny_talwar — Fri, 10 Aug 2018 16:20:49 GMT

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

Re: Week Number Before Certain Week

Anonymous — Fri, 10 Aug 2018 16:46:58 GMT

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 ..

Re: Week Number Before Certain Week

sunny_talwar — Fri, 10 Aug 2018 16:49:12 GMT

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

Re: Week Number Before Certain Week

Anonymous — Fri, 10 Aug 2018 16:58:25 GMT

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