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!
You could use 2 prompts with resp. the parameters:
?dayoftheweek? (list of 7 values) and ?date_select?
The filter would be something like:
_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?
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!
_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:
select store,'Sales today',null,sum(sales) from ...........
where [somedate] = current date
group by store
2nd union part:
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
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
On second thought , this one can be solved through a regular crosstab:
select store,'Sales today',sum(sales) from ...........
where [somedate] = current date
group by store
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.