Sorting Crosstab Query Column Headers with DatePart Function

[jwreding]

My goal: A crosstab query with weeks as column headers, showing a rolling 16 week report. Therefore, the most recent week would be column 1, previous week column 2, etc. back to 16 weeks ago.



I am familiar with the method of forcing crosstab column headers to sort properly. Since I need the columns to change each week, showing the most recent week first and pushing each subsequent week out to the right, setting static column headers was a challenge. I was able to accomplish this by subtracting the current week number from the week number of the dates of the records, meaning I would have a result of -1, -2, etc. Then I could statically set the column headings as those numbers.

Well this all broke down when the year switched over. Obviously subtracting Week 1 from Week 48 and such does not yield the correct result. To add even further complexity, the "week 53" issue is not helping matters either. I've tried several ways of working around this, but each one yields either an incorrect result, or a solution that I will have to modify near the end of the year.

The best solution I have come up with so far abandons the static column headers. I have set my column headers to be Format([PickupDate],"yyyy-ww") and sorting in Descending order. It ALMOST works, but no matter what I do, I get two separate columns for the first and/or last week of the year. I have played with the [firstdayofweek] and [firstdayofyear] settings, but no matter what I always get either a "2014-1" and "2013-1" combination, or a "2013-52" and "2013-53" combo. Access seems to show Dec 29-31,2013 as either "Week 1" of 2013 (which makes no logical sense but I can see why it's happening), or "Week 53" of 2013 no matter what settings I use. If I could only figure out a way to combine those two partial weeks into one, I would have an acceptable solution (although I'd rather find a way to use static column headings in a perfect world).

TL;DR - How can I get Access to recognize the week of Dec 29-Jan 4 as ONE contiguous week? Again, I have tried all of the settings in the FormatDate function to tweak the starting day of the week, etc. and I always get an additional "Week 53 of 2013" or "Week 1 of 2013" no matter what.

Thanks in advance and I will be online all day to provide further explanation or post a file if necessary.

[rpeare]

can you provide a sample of your database with a working crosstab as you have it now.

Just make a copy of your database, take out any information that's not relevant to this problem, make sure you have enough data to replicate the problem, then compact/repair, zip and upload the file to this website.

[jwreding]

File attached. As you see, I have tweaked the settings for the Format() function. Currently it is giving me a week 53 of 2013. Other settings give me a week 1 of 2013, even though it's really the last partial week of 2013 and first partial week of 2014.

Thank you rpeare.

[rpeare]

Hrm... with this query you are not choosing a static set of weeks. For instance if you ran this today, then ran it again tomorrow you'd get different results even if none of the data had changed because some data would have moved weeks, is that what you intended? Or are you taking greater pains to suppress 'wrong' date selections in your actual database?

You have two possibilities with this query

1. The year ends ends on a saturday which coincides exactly with your week function so you have no problems (ends at 52 weeks)
2. the year doesn't end on a saturday and you have a problem (ends at 53 weeks)

What your query is actually doing is throwing december 29, 30 and 31 into week 53 of the 'old year' and january 1, 2, 3 and 4 into week 1 of the new year.

so try this:

TRANSFORM Count(Loads.PRO) AS CountOfPRO
SELECT Loads.CustomerID
FROM Loads
WHERE (((Loads.PickupDate) Between Date()-112 And Date()))
GROUP BY Loads.CustomerID
ORDER BY IIf(DatePart("ww",[pickupdate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"yyyy-ww")),Format([pickupdate],"yyyy-ww")) DESC 
PIVOT IIf(DatePart("ww",[pickupdate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"yyyy-ww")),Format([pickupdate],"yyyy-ww"));

I'm basically saying that if the week is 53, convert the date of the first day of the year's week rather than using the 53 value.

[rpeare]

sorry I didn't say that first part very well, if you ran this today, then again tomorrow, some of the data on the 'first' week would have dropped off and some of the data on the last week would have changed as well.

[jwreding]

In most of my queries, the WHERE clause is actually

"WHERE ((DatePart("ww", [Loads].[PickupDate] Between DatePart("ww", Date()-112 And DatePart("ww", Date()))"

But I removed all of the DatePart stuff just for clarity while I was trying to solve this other problem. I don't believe it's related to the particular issue I'm talking about regarding the column headings. Note the above is not syntactically correct because I just typed it in here, but you get the idea.


Since I'm using the "ww" part of the date, it considers any dates from Sun-Sat of a particular week to be in that week number. So (if I'm correct) the query results won't change until this Sunday, when the data that is currently 1 week old will now be 2 weeks old.

You are correct that I don't want a static set of weeks. People want to look at the query and see live rolling data going back a certain number of weeks (16). Then that 16th week will "fall off" the query when the next week rolls around. Basically looking at trends comparing week to week.

But all of the above babbling is unnecessary, because I tried your SQL and it seems to work perfectly! Even better, from reading it I understand what you're doing there so it will help me with future queries.

Thanks very much.

[jwreding]

Follow up...

Will converting any week 53 dates to the first week of the year create problems since Access sees it as week 53 of 2013? Will it convert these to week 1 of 2013 or 2014? I am thinking 2013 since you are using the year of the pickupdate, which would be 2013. Is it necessary to add 1 to the year when converting week 53?

Looking at the results of your query, it doesn't show any values for week 1 of 2013, so it is apparently working correctly. I am just trying to understand why.

[rpeare]

Any days that would fall into week 53 of any given year should be grouped into the first week of the following year based on the formula I gave you.

That formula, if used in other queries should give the same result. You can test this by just building a query based on your form and putting in the formula I gave you

like this:

SELECT Loads.PRO, Loads.CustomerID, Loads.PickupDate, IIf(DatePart("ww",[pickupdate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"yyyy-ww")),Format([pickupdate],"yyyy-ww")) AS Expr1
FROM Loads
ORDER BY Loads.PickupDate DESC;

[jwreding]

Thanks...and yes it does appear to work. I was looking from more of an academic/learning standpoint now since the problem is solved (and I'll mark it as such as soon as I find where to do so).

Breaking apart the expression in my mind, it is saying that IF the week of the PickupDate is 53 then it uses CDate to create a new date of 1/1/yyyy, using the year of the PickupDate. So for a date such as 12/29/2013, which is in week 53 of 2013, I would think it would see that as week 53, then build a new date of 1/1/2013, since the value of "yyyy" for this PickupDate is 2013, not 2014. I am missing where it is converting the year of the PickupDate in the record to the subsequent year.

OH. I see what's happening now. I was misreading the CDate expression. It converts to the current year, not the year of the PickupDate! I understand now. I was about to delete this reply, but thought I would post it anyway in case anyone else has the same question.

Thanks again rpeare.

[rpeare]

no problemo, I frequently break down my formulas into smaller parts before putting them together, helps make sure I"m getting the right result as I go along.

[jwreding]

A follow up note to anyone interested, or who may search this topic at a later date:

I was finally able to achieve the static column headings using rpeare's tip on converting week 53 to 52 and other uses of the Format() function. The ridiculous if statement below will give you a rolling 16 week (or any number of weeks depending on the date criteria you use) report with static column headings that show "-1" for last week, "-2" for 2 weeks ago, etc. This is nice because it allows you to actually make a form based on your query to add formatting and other expressions in text boxes (like % growth and such).

The if statement:

IIf(IIf(DatePart("ww",[PickupDate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"ww")),Format([PickupDate],"ww")) - DatePart("ww", Date()) >=0, IIf(DatePart("ww",[PickupDate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"ww")),Format([PickupDate],"ww")) - (DatePart("ww", Date()) + 52), IIf(DatePart("ww",[PickupDate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"ww")),Format([PickupDate],"ww")) - (DatePart("ww", Date()))

In English, it subtracts the current week number from the week of the date on the record (in this case PickupDate). If the result is positive (as would happen when you move across a changing year, e.g. week 51 - week 2), it simply adds 52 to the current week number. If the result is negative, it does the original operation. It's really not a complex expression, it's just a very long sub-expression repeated many times.

Thanks again and I hope someone else is helped by this in the future. I know for my company specifically, it is a great metric to track attrition and other sales/profit trends in a manner such as this.