Sum/Avg Sales data by day of week?

[bmurch71]

Hi,
I have a sales by department history table with date, dept number and sales total. I'm trying to create a report that will calculate a running average of sales by department and day of week for the last four weeks. In other words, I want a report that will show the average of the last 4 Mondays of sales in the grocery department - and so on for the other days of the week and other departments. Anyone have any clever solutions to this?
Thanks,
Brian

 

[bmurch71]

A little more info:
This same table also has agregate sales data for the week, the month and the year to date. There is a field that identifies if the record is a daily, weekly, monthly or yearly total. I thought that perhaps I could use a weekly total record (which includes the week ending date) as a trigger for other processes, but I wasn't sure how to make that work. Just in case that helps . . .
Brian

 

[Andrew Fergus]

Hi Brian
You should be able to do this with a crosstab query. I can't provide a precise answer without knowing the field names in your table but it would work like this :

1. Create a crosstab query based on your sales history table.
2. Add the departments (code and description?) and set that to the row heading.
3. Add the date field and include the criteria :
>=DateAdd("ww",-4,Date())
do not set this to either the row or column heading. This filters the data for the past 4 weeks.
4. Add the following new column :
WeekDay : Weekday([date])
but use your actual date field name where I have used 'date'. Set this to be the column heading. This returns a value of 1 to 7 where 1 is Sunday.
5. Right click the weekday field > select properties > under column headings enter :
1,2,3,4,5,6,7
This will ensure a day is included in the query even if there is no data for that day.
6. Add the sales value field, change the group by to 'Avg' and set the crosstab property to 'Value'.
7. Run and test this query.
8. If you are happy with the query, build a report based on this query.

HTH, Andrew

 

[bmurch71]

Hi Andrew,
Thanks for the help. Here's what I came up with:

TRANSFORM Avg(qry_DeptSalesByDay.Sales) AS AvgOfSales
SELECT qry_DeptSalesByDay.DEPT, qry_DeptSalesByDay.Dept_Name
FROM qry_DeptSalesByDay
WHERE (((qry_DeptSalesByDay.DATE)>=DateAdd("ww",-4,Date())))
GROUP BY qry_DeptSalesByDay.DEPT, qry_DeptSalesByDay.Dept_Name, qry_DeptSalesByDay.DATE
PIVOT Weekday([date]) In (1,2,3,4,5,6,7);

The "qry_DeptSalesByDay" is a SQL pass-through query to my SQL table with the data in it. When I run this, however, I get multiple rows for each department - 28 rows, or 4 weeks worth of data. Did I miss something in your description, or is perhaps my pass-through query to blame? Here's the SQL for the pass-through query:

SELECT RPT_DPT.F254 AS [DATE], RPT_DPT.F03 AS DEPT, Sum(RPT_DPT.F65) AS Sales, DEPT_TAB.F238 AS Dept_Name
FROM RPT_DPT INNER JOIN DEPT_TAB ON RPT_DPT.F03 = DEPT_TAB.F03
WHERE (((RPT_DPT.F1031)='D'))
GROUP BY RPT_DPT.F254, RPT_DPT.F03, DEPT_TAB.F238
ORDER BY RPT_DPT.F254, RPT_DPT.F03;

You can see that the SQL uses the extremely helpful "Letter/Number" designation for field names! /sarcasm
Any ideas?
Thanks,
Brian

 

[bmurch71]

Ha!
I figured it out - sorry, Andrew, I should have looked more closely at my query. There was an extra Date column in there that was set to group by. Thanks for your help again - I really appreciate it.
Brian