How do I fix this variable? Charts for last 4 weeks of data.

[#1]

This is my variable:

=if (Week([Time Created]) - 1 > Week([Time Created]),Year([Time Created]) -1,Year([Time Created]))

[Time Created] is just a date in the format of MM/DD/YYYY

This variable should return to me the proper year of the week prior to the selected [Time Created].

From what I can tell, because the Week function ends on a Sunday and Sunday this year was actually January 1, 2012 there is an issue with it determining the proper Week to use.

Update:
So i've realized that this isn't illustrating the entire problem. I have three variables total, similar to the one above. Each are used so that I can calculate the year 1 week, 2 weeks, and 3 weeks prior to my selected date. This way I'm able to have four charts. 1 for the current week and the rest for the weeks leading up to that selected week. The issue with using the week function is it returns the numerical week of the year. Being that I have multiple years I needed to calculate the proper year to associate that week with, otherwise I get a certain week for all years.

Because of this issue of having a week occupy two different years.... I'm now no longer convinced I should be using the week function at all. Does anyone have experience doing what I'm trying to do? Maybe it just makes sense to subtract days from the start of the initial selected week.

[swuehl]

Yes, could be the solution (I am still not quite sure what you are after):

=year([Time Created]-7) for the year one week back.

or

=year(weekstart([Time Created],-1)) for the year of the weekstart one week back.

Or look into

weekyear(date) function?

[#3]

basically i'm trying to do this in my expression:

Count({$<[Week Created]={$(#=Week([Time Created])-1)}, [Year Created]={$(#YrWk-1)}, [Time Created]=>} DISTINCT [Ticket#])

where YrWk-1 is the above variable. The problem is when [Week Created] = 52 with [Year Created] = 2011 but that same week also contains day 1 of week 1 of 2012.

So, if my selected [Time Created] is 01/01/2012 then it is going to give me a [Week Created] = 1 and a [Year Created] = 2012 but if the [Time Created] is 12/31/2011 it'll will give me [Week Created] = 52 and [Year Created] = 2011 but it will not give me the data for 01/01/2012 even though that belongs to the same week that ends on sunday.

[swuehl · Accepted solution]

I believe calendar and especially week numbering is handled in QV according ISO 8601.

So if you say, 01/01/2012 is giving you [Week Created]=1, this is not the case if you just use =week(makedate(2012,1,1))

which will give you week# 52. So you probably are using some other calendar system, maybe US? Please specify how you defined your week#, including the expressions in the script then.

If this all is not the case and I am wrong on my assumptions, you could look into the weekyear() function as suggested above.

From the Help:

weekyear(date)

The year to which the week number belongs. The week number ranges between 1 and approximately 52. Some years week #1 starts in December, e.g. December 1997. Other years start with week #53 of previous year, e.g. January 1999. For those few days when the week number belongs to another year, the functions year and weekyear will return different values.

But if you just want to select the previous week, it might be easier to do:

=count( {$< [Time Created] = {">=$(=weekstart([Time Created],-1))<=$(=weekend([Time Created],-1))"} >} distinct [Ticket#] )

You may need to clear selections on other date fields too:

=count( {$< [Time Created] = {">=$(=weekstart([Time Created],-1))<=$(=weekend([Time Created],-1))"}

,[Week Created]=, [Year Created]= >} distinct [Ticket#] )

Hope this helps,

Stefan

edit:

weekstart() and weekend() will return Timestamps (e.g. a value corresponding to a timestamp with the last millisecond of the last date ( Sunday ) of the calendar week containing the input date parameter). So you might need to convert to Dates using daystart and also format the output according your [Time Created] format. If your [Time Created] format is DD/MM/YYYY and this is not your standard format, it might look like:

=count( {$< [Time Created] = {">=$(=Date(weekstart([Time Created],-1),'DD/MM/YYYY'))<=$(=Date(daystart(weekend([Time Created],-1)),'DD/MM/YYYY))"}

,[Week Created]=, [Year Created]= >} distinct [Ticket#] )

Maybe not all is necessary ( I haven't tried the expressions in real life), but I often fight with date format issues like that.

[#5]

Thank you Stefan, this helps very much. I'll test this out and let you know if its what I need.

Edit: Works! Thank you so much for your help! I was always wondering how to denote a range in the set notation, that >= <= bit also will help me immensly. Thanks again.