Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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"
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"
Script, I need users to be able to reuse the expression. I will try these and see if it fits my need. thanks
Okay, so really close. So it looks like their week starts on a sunday, not monday. What would need to be changed?
I just needed to add +1 to the Date and it worked like I expected. Thanks John!
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.
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')
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.