topic Re: Flag every other week in QlikView

Flag every other week

Mon, 07 Nov 2011 22:32:29 GMT

Is there a way to flag every other week?

Here is an example of what I want to do. Every other week is labeled City. An inline with an if statement of some sort? What function could I use?

DATE

DAY

CITY/NON CITY

1/1/2008	TUESDAY	NON
1/2/2008	WEDNESDAY	NON
1/3/2008	THURSDAY	NON
1/4/2008	FRIDAY	NON
1/5/2008	SATURDAY	NON
1/6/2008	SUNDAY	CITY
1/7/2008	MONDAY	CITY
1/8/2008	TUESDAY	CITY
1/9/2008	WEDNESDAY	CITY
1/10/2008	THURSDAY	CITY
1/11/2008	FRIDAY	CITY
1/12/2008	SATURDAY	CITY
1/13/2008	SUNDAY	NON
1/14/2008	MONDAY	NON
1/15/2008	TUESDAY	NON
1/16/2008	WEDNESDAY	NON
1/17/2008	THURSDAY	NON
1/18/2008	FRIDAY	NON
1/19/2008	SATURDAY	NON
1/20/2008	SUNDAY	CITY
1/21/2008	MONDAY	CITY
1/22/2008	TUESDAY	CITY
1/23/2008	WEDNESDAY	CITY
1/24/2008	THURSDAY	CITY
1/25/2008	FRIDAY	CITY
1/26/2008	SATURDAY	CITY
1/27/2008	SUNDAY	NON
1/28/2008	MONDAY	NON
1/29/2008	TUESDAY	NON
1/30/2008	WEDNESDAY	NON
1/31/2008	THURSDAY	NON

Re: Flag every other week

johnw — Mon, 07 Nov 2011 22:50:04 GMT

Do you want a chart expression? Maybe something like this:

if(weekstart(DATE)=weekstart(above(DATE)),above("CITY/NON CITY"),if(above("CITY/NON CITY")='NON','CITY','NON'))

In the script, it would be similar, maybe something like this in your calendar table:

if(weekstart(DATE)=weekstart(previous(DATE)),peek("CITY/NON CITY"),if(peek("CITY/NON CITY")='NON','CITY','NON')) as "CITY/NON CITY"

Re: Flag every other week

Mon, 07 Nov 2011 23:57:24 GMT

Script, I need users to be able to reuse the expression. I will try these and see if it fits my need. thanks

Re: Flag every other week

Tue, 08 Nov 2011 18:16:56 GMT

Okay, so really close. So it looks like their week starts on a sunday, not monday. What would need to be changed?

Re: Flag every other week

Tue, 08 Nov 2011 18:22:53 GMT

I just needed to add +1 to the Date and it worked like I expected. Thanks John!

Re: Flag every other week

johnw — Tue, 08 Nov 2011 18:27:19 GMT

The third parameter of the weekstart() lets you say which day the week starts on. For weeks starting on Sunday, use weekstart(Date,0,-1). Adding a day might be sufficient in your case, but it's probably good to get in the habit of using the QlikView functions. No big deal, though. Just wanted to point it out.

Flag every other week

Sat, 12 Nov 2011 16:19:11 GMT

If this satisfies your requirement it would be a little simpler. You could try an odd or even week approach =if(odd(week(DATE)),'CITY','NON')

Flag every other week

johnw — Tue, 15 Nov 2011 00:31:37 GMT

The week() function doesn't have a parameter to allow it to start on Sunday, though perhaps the +1 cheat is fine since we don't actually care what the week number is. But then we have the problem where it won't always flag every other week because sometimes the year ends with week 53.