Group by Week Graph

[primagic]

I have the following query which displays information in a bar graph by week number.

SELECT (Format([MyGroupDate],"ww")) AS Expr1, Sum(qryrptAWeeklyGraph.CountOfID) AS SumOfCountOfID
FROM qryrptAWeeklyGraph
GROUP BY (Format([MyGroupDate],"ww")), (Year([MyGroupDate])*CLng(54)+DatePart("ww",[MyGroupDate],0)-1);

However if I change the sql to:

SELECT (Format([MyGroupDate],"dd/mm/yy")) AS Expr1, Sum(qryrptAWeeklyGraph.CountOfID) AS SumOfCountOfID
FROM qryrptAWeeklyGraph
GROUP BY (Format([MyGroupDate],"ww")), (Year([MyGroupDate])*CLng(54)+DatePart("ww",[MyGroupDate],0)-1);

Where the format is change to dd/mm/yy then instead of just displaying the first day of the week, it displays all the days in between with 0 values as well as the total amount for each week on the first day.

How can I show the graph to display the first day of the week and corresponding total(sum of events) for that week without all the days in between.

Hope this makes sense

Thanks

 

[lameid]

To your first query, try adding a value that formats the minimum of My Group Date...

This will work if you reliably have the first day of the week in your data.... Otherwise you would need to have a table with a list of days to use in a similar way.

 

[primagic]

Sorry, I dont understand what you mean by adding a value that formats the minimum of my group date

Thanks again

 

[Qik3Coder]

the actual day of the work

Format([MyGroupDate],"dd/mm/yy")) AS Expr1

The week the work occurred

(Format([MyGroupDate],"ww")) AS Expr1

lameid was indicating that if you did the following you would always have the first day work occurred on, per week, but ideally you would have a datapoint for the first day of the week, so as not to confuse others. This would not have to be included in the group by as it is an aggregating column

MIN(Format([MyGroupDate],"dd/mm/yy"))) AS Expr1

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[primagic]

This is my query now:

SELECT MIN(Format([MyGroupDate],"dd/mm/yy")) AS Expr1, Sum(qryrptAWeeklyGraph.CountOfID) AS SumOfCountOfID
FROM qryrptAWeeklyGraph
GROUP BY (Year([MyGroupDate])*CLng(54)+DatePart("ww",[MyGroupDate],0)-1);

However, in the report it displays the date for the beginning of the week so if I want values between 01/01/2010 and 31/01/2010 it shows amounts correctly on the following dates - 04/01, 11/01, 18/01 & 25/01. but it still shows all the dates in between those 4 dates. I just wanted it to display the 4 days with values

 

[Qik3Coder]

You only need the GROUP BY section when you have non-aggregated columns, to "squish" them.

Skinny answer: Remove GROUP BY

Long Answer: Your GROUP BY has a different value than one of your columns, so it is causing extra splitting/partitioning of the rows. If you had additional columns like Department, region, quarter, then you would want those in the group by so that you could see sales by those sections.

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[lameid]

I don't think I followed the last 2 posts but this is what I meant...

SELECT (Format([MyGroupDate],"ww")) AS Expr1,
 Sum(qryrptAWeeklyGraph.CountOfID) AS SumOfCountOfID,
 (Format(Min([MyGroupDate]),"dd/mm/yy")) as FirstDayOfWeek

FROM qryrptAWeeklyGraph

GROUP BY (Format([MyGroupDate],"ww")), (Year([MyGroupDate])*CLng(54)+DatePart("ww",[MyGroupDate],0)-1);

Notice how the format is on the outside of the minimum versus vice versa. Probably the same result but I don't like taking the minimum of strings which is what format returns.