Dynamic weekly date range in crosstab report

[ollyb303]

Hello,

I am using a dynamic crosstab report to track performance statistics for my company and I have hit a problem.

I would like the option to track stats daily (for the last 7 complete days), weekly (for the last 6 weeks) and monthly (for the last 6 complete months).

Daily and monthly are not causing me a problem - I have used the following code to construct the query:

strXT = "TRANSFORM Sum(Query2.STAT) AS SumOfSTAT " & _
"SELECT Query2.Site, Query2.OM, Query2.TM, Query2.Name " & _
"FROM Query2 " & _
"GROUP BY Query2.Site, Query2.OM, Query2.TM, Query2.Name " & _
strPivot & ";"

And the following to create the "strPivot" string:

Daily:

strPivot = "PIVOT [DATE]"

Monthly:

    If Format(strStopDate, "MMM") = "Jan" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Aug"", ""Sep"", ""Oct"", ""Nov"", ""Dec"", ""Jan"")"
    End If
    If Format(strStopDate, "MMM") = "Feb" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Sep"", ""Oct"", ""Nov"", ""Dec"", ""Jan"", ""Feb"")"
    End If
    If Format(strStopDate, "MMM") = "Mar" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Oct"", ""Nov"", ""Dec"", ""Jan"", ""Feb"", ""Mar"")"
    End If
    If Format(strStopDate, "MMM") = "Apr" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Nov"", ""Dec"", ""Jan"", ""Feb"", ""Mar"", ""Apr"")"
    End If
    If Format(strStopDate, "MMM") = "May" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Dec"", ""Jan"", ""Feb"", ""Mar"", ""Apr"", ""May"")"
    End If
    If Format(strStopDate, "MMM") = "Jun" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jan"", ""Feb"", ""Mar"", ""Apr"", ""May"", ""Jun"")"
    End If
    If Format(strStopDate, "MMM") = "Jul" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Feb"", ""Mar"", ""Apr"", ""May"", ""Jun"", ""Jul"")"
    End If
    If Format(strStopDate, "MMM") = "Aug" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Mar"", ""Apr"", ""May"", ""Jun"", ""Jul"", ""Aug"")"
    End If
    If Format(strStopDate, "MMM") = "Sep" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Apr"", ""May"", ""Jun"", ""Jul"", ""Aug"", ""Sep"")"
    End If
    If Format(strStopDate, "MMM") = "Oct" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""May"", ""Jun"", ""Jul"", ""Aug"", ""Sep"", ""Oct"")"
    End If
    If Format(strStopDate, "MMM") = "Nov" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jun"", ""Jul"", ""Aug"", ""Sep"", ""Oct"", ""Nov"")"
    End If
    If Format(strStopDate, "MMM") = "Dec" Then
        strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jul"", ""Aug"", ""Sep"", ""Oct"", ""Nov"", ""Dec"")"
    End If

I am now stuck with how to create the pivot for a dynamic weekly range. Ideally I would like the user to select an end date (any date up to and including yesterday) and the report would give a total for each of the last 6 weeks - preferably the week could run from any day, but if I have to use Mon-Sun that would also be acceptable. Can anyone help me with this? I've googled like there's no tomorrow and I'm not having any luck!

I can supply any more information/code if required.

Many thanks,

Olly

[ollyb303]

Just a little bump! Can anyone help me with this? Please....?

[ChipR]

What about [DATE] BETWEEN (EndDate - 42) AND EndDate

[ollyb303]

Thanks Chip, but that would give me a daily total for each day in the 6 week date range (if used in the pivot). What I'm after is a weekly total for each of 6 weeks.

Any other ideas?

Many thanks

[ollyb303]

Still at a loss with this one.... anyone?

[ollyb303]

So I'm guessing there's no way to do what I want to do in this case :(

Think I may have to give up on this idea unless anyone wants to chime in?

[Stewart Ross]

Hi Olly. You can calculate a weekly interval in the base query on which you are totalling - for example by using

DateDiff("w", [your transaction date], date()).

If you put a where clause of <=6 on this you will restrict the query to the last 6 complete weeks. You can then pivot the data on the number of weeks - 0 is the current week (which is generally incomplete of course), 1 the previous week and so on, totalling the transactions for each week as appropriate.

I leave it for you try this out on your own data and to do some form of week-beginning calculation for your pivot headers. I've tried this approach on a simple transaction count basis (producing the number of transactions received in each week) and it works fine for me (without fancy weekdate headers).

-Stewart

[Stewart Ross]

... and further to the above here is a function which will return the start date of the current week. It can be used to provide custom date headers for use with your datediff-based calculations:

Public Function fStartofWeek(somedate, Optional StartDayNo = 2)
    'Returns the date of the first day of the current week
    'Default is to use the system-defined start day no of 2
    '(corresponding to Monday). Range of 1 to 7 for this value.
    '
    Dim intDayOfWeek As Integer, intAdjustDays As Integer
    If Not IsNull(somedate) Then
        intDayOfWeek = Weekday(somedate)
        intAdjustDays = intDayOfWeek - StartDayNo
        If intAdjustDays < 0 Then
            intAdjustDays = intAdjustDays + 7
        End If
        fStartofWeek = CDate(somedate - intAdjustDays)
    End If
End Function

An example query based on a test table (no relation to your own), which uses a custom format for the date to order the crosstab correctly:

TRANSFORM Count(tblTemp.CallTime) AS CountOfCallTime
SELECT "Test" AS TestGroup
FROM tblTemp
WHERE (((DateDiff("w",fStartOfWeek([CallDate]),Date()))<=6))
GROUP BY "Test"
PIVOT Format(fStartofWeek([CallDate]),"yyyy-mm-dd");

And the result of that query:

TestGroup 2008-12-22 2008-12-29 2009-01-05 2009-01-12 2009-01-19 2009-01-26 2009-02-02 2009-02-09
Test              78        219        227        221        206        232        187         85

-Stewart

[ollyb303]

Stewart, thank you so much!

As I'm sure you can tell, I'd pretty much given up hope of this working!

Haven't tried it out yet, but it looks very promising - I'll let you know how I get on.

Thanks again,

Olly

[ollyb303]

Hi Olly. You can calculate a weekly interval in the base query on which you are totalling - for example by using

DateDiff("w", [your transaction date], date()).

If you put a where clause of <=6 on this you will restrict the query to the last 6 complete weeks. You can then pivot the data on the number of weeks - 0 is the current week (which is generally incomplete of course), 1 the previous week and so on, totalling the transactions for each week as appropriate.

I leave it for you try this out on your own data and to do some form of week-beginning calculation for your pivot headers. I've tried this approach on a simple transaction count basis (producing the number of transactions received in each week) and it works fine for me (without fancy weekdate headers).

-Stewart

Hi Stewart,

I've tried this and I have a new problem...

I should have mentioned that my initial query is actually a SQL pass-through to an Oracle db, so (as I now realise) DateDiff won't work.

Do you know how to write this:
DateDiff("w", [your transaction date], date()).

In a syntax Oracle will accept?

Many thanks.

[Stewart Ross]

Hi Olly. I can only suggest that you feed the result of the pass-through query to an Access query (using the pass-through to provide a view of the Oracle data) and perform the calculations in Access - once the data has been obtained from the Oracle back-end DB Access should be able to process it as normal.

The original Oracle query would not be able to handle the date function I have suggested either I guess...

-Stewart

[ollyb303]

Good idea. Thanks very much for your help.