Hi All,
I am wring a dash board for a shopping centre and need to predict what the average sales of a store based on the average sales based on the stores retail Category.
For most stores I can calculate the 52 weeks of rolling sales data. (ie the sum of the last 52 week)
sum({$<[Week Ending]= {">=$(=(date([Week Ending]-52*7)))<=$(Max([Week Ending]))"}> } SalesGross)
However for other stores I only have a partial set of data because the store has been trading less than one year. So I need to work out a predicted annual sales for these stores based on a partial set of results. I could just divid the total sales I do have for the store by the number of weeks they have been trading and then multiply by 52 to get an annual total.
Unfortunately, as most of you are aware, retail sales are not linear throughout the year. Ie the 12 weeks up to Christmas, can represent as much as 70% of a stores annual turnover .
So what I would like to do is the following.
1) Work out the total sales for the [Retail Category] for the same period that a store has been trading . ie if the stare has been trading from April to September, what is the total category sales from April to September)
2) Divide the total in 1) above by the total [Retail Category] sales for the entire rolling 52 week period to obtain the proportion or percentage of retail category that was achieved in the same trading period.
3) Divide the Total sales for the store by the % of sales for the [Retail Category]
My problem is that this has become too complex for my limited understanding for Set analysis so I would be greatful if somebody could point me in the right direction.
Many Thanks,
Lawerence
