charts by Week

[AccidentalFate]

making a chart amount on the Y axis and date by week on the X axis... when i do this the date shows up as the week number instead of date (34 '07 for example) can i change this to a date instead?

 

[AccidentalFate]

this is what it looks like... i cant seem to change it correctly to work....

SELECT (Format([FT Date],"WW 'YY")),Sum([SumOfSum Of From]) AS [SumOfSumOfSum Of From] FROM [Chart Abs 1 T] GROUP BY (Year([FT Date])*CLng(54) + DatePart("ww",[FT Date],0)-1),(Format([FT Date],"WW 'YY"));

any help?

 

[Rabbie]

Change your Format staementsto display the dates in the way you want. More info available in the Help files.

 

[AccidentalFate]

i tried... i cant get it to work correctly... i can change it to show daily but i need it to group by week still jus to show the date not the week number

 

[AccidentalFate]

can you help?

 

[AccidentalFate]

TRANSFORM Sum([Chart Abs T].[SumOfSum Of From]) AS SumOfTerms
SELECT (Format([FT Date],"ww"" '""yy")) AS Expr1
FROM [Chart Abs T]
GROUP BY (Year([FT Date])*CLng(54)+DatePart("ww",[FT Date],0)-1), (Format([FT Date],"ww"" '""yy"))
PIVOT [Chart Abs T].clientid;

this is what it looks like in sql i hope it's easier to look at... please hlep

 

[namliam]

Thanks for your PM, I feel flattered

If all you want to do is "roll up" all dates in a week to one date, i.e. the monday... then try using this expression:
[FT Date] - Weekday([FT Date], vbmonday) + 1

If [FT Date] is today, Weekday function will return 2, because it is tuesday, which is the second day of the week because I used vbMonday. It would be third day of the week if you use vbSunday.
Deductin 2 days from today makes it sunday, so you have to add 1 again to get to monday.

I hope that is clear enough

 

[AccidentalFate]

TRANSFORM Sum([Chart Abs T].[SumOfSum Of From]) AS SumOfTerms
SELECT (Format([FT Date],"ww"" '""yy")) AS Expr1
FROM [Chart Abs T]
GROUP BY (Year([FT Date])*CLng(54)+weekday([FT Date],vbmonday)+1), (Format([FT Date],"ww"" '""yy"))
PIVOT [Chart Abs T].clientid;

like this?
i'm new to all of this by the way

 

[AccidentalFate]

this is my chart as of now... i want to change the week number to a date

 

[namliam]

TRANSFORM Sum([Chart Abs T].[SumOfSum Of From]) AS SumOfTerms
SELECT [FT Date] - Weekday([FT Date], vbmonday) + 1 AS Expr1
FROM [Chart Abs T]
GROUP BY [FT Date] - Weekday([FT Date], vbmonday) + 1
PIVOT [Chart Abs T].clientid;

Something like so... I think...

 

[AccidentalFate]

Thanks...

i got it to work with this...

TRANSFORM Sum([Chart Abs T].[Sum Of From]) AS [SumOfTerms]
SELECT ([ft Date]-Weekday([ft Date])+7) AS Expr1
FROM [Chart Abs T]
GROUP BY (Year([FT Date])*CLng(54)+DatePart("ww",[FT Date],0)-1), ([ft Date]-Weekday([ft Date])+7)
PIVOT [Chart Abs T].clientid;

Very simular

 

[namliam]

I have absolutely no clue what this part does...
(Year([FT Date])*CLng(54)+DatePart("ww",[FT Date],0)-1),

It seems to me to be there without real use... But as long as you have it working as you like... That is the most important part.

Dont you find the missing weeks/dates rather irritating? With all the broken lines and single dots in your graph...