Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
dmelillo
Contributor III

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?

Tags (1)
4 Replies

Re: Week Number Before Certain Week

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

dmelillo
Contributor III

Re: Week Number Before Certain Week

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

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

dmelillo
Contributor III

Re: Week Number Before Certain Week

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