Query for past 24 same day sales figures and averages

[ezgrand]

Hello Everyone,

I have created a historical average report and would like to improve on it
by deriving the past 24 same day sales figures so that I could come  up
with averages of the past 24 same day sales.

For example, for Friday, June 20, I need to get the sales figures of the past 24 Fridays e.g. June 13, Jun6 , May 30, etc. Then, I can divide this with 24 to arrive at the past 24 same day of the week ave.

TIA!

[blom0344]

You could use 2 prompts with resp. the parameters:

?dayoftheweek? (list of 7 values) and ?date_select?

The filter would be something like:

Code Select Expand


_day_of_week([somedate],1) = ?dayoftheweek? and
_add_days(?date_select?,-7*24) <= [somedate]


Actually should be possible with just 1 prompt and the right functions as well. But you get the general idea?

[ezgrand]

Thanks for the reply.  I actually do not intend to show all the data.  The report runs automatically  everyday and looks like this:

Store      Sales(Fri, 6/20)  Ave Sales(of the last 24 Fridays e.g. 6/13 6/6)
x                x                            x
xx              xx                          xx
xxx            xxx                         xxx

As such, I am stuck and unable to use prompts and must use a query for the average sales column.

thanks!

[blom0344]

Code Select Expand


_day_of_week([somedate],1) =  _day_of_week([current date],1)and
_add_days(?date_select?,-7*24) <= [current date]


Or something along these lines for the query filter.

Having this all in 1 final query:

1st union part:

Code Select Expand


select store,'Sales today',null,sum(sales) from ...........
where [somedate] = current date
group by store


2nd union part:

Code Select Expand


select store,null,'Avg sales',sum(sales) from ...........
where _day_of_week([somedate],1) =  _day_of_week([current date],1)and
_add_days(?date_select?,-7*24) <= [current date]
group by store


Create 3rd query based omn the two union parts and build discontinuous crosstab

[ezgrand]

Hello blom0344,

Thanks for the assistance on my report.  I am attaching the sample specs of the report .  I followed most of your suggestions except for some tweaking to speed up the query.

I am now stuck--of all things on how to build the disjoint crosstab! I tried the help files in Report studio. No luck. 

To recap, query 1 solves for the historical 24 week same day average sales, while query two solves for yesterday's sales. 

I need to show the average and the yesterday's sales in one report(the disjoint crosstab) and get the ratio(the easy part!)

Please show me how this is done!

TIA

[blom0344]

On second thought , this one can be solved through a regular crosstab:

Code Select Expand


select store,'Sales today',sum(sales) from ...........
where [somedate] = current date
group by store


Code Select Expand



select store,'Avg sales',sum(sales) from ...........
where _day_of_week([somedate],1) =  _day_of_week([current date],1)and
_add_days(?date_select?,-7*24) <= [current date]
group by store


Just create a union on this and use the second query item (that stores values 'Sales Today' and
'Avg Sales') in the crosstab column.