I would like to do a forecast. All the forecasting
examples that I have read will not work for our retail
company. For example, I want to forecast for the month of
December 2004. I want to take last year's (December 2003
and divide it by December 2002) minus 1. This will give me
the percent growth from December 2002 to December 2003.
Then, I will take December 2003 and add the percent growth
to arrive at December 2004 forecast. This very simple
forecast will reflect seasonality. I am aware of
ParallelPeriod, which will look at December 2003 (12
months ago), when I am looking a December 2004. I also
need something to look at December 2002 (24 months ago).
Then I can create the formula. The formula needs to be
dynamic, not lock down on a single month. I will observe
all solutions or workarounds? Help will be appreciated
much!

TC

Here is something similar to your scenario from Foodmart
(but replace 'Quarter' with 'Year' for your forecast):

as 'LinRegPoint(3,
{ParallelPeriod(Quarter,2), ParallelPeriod(Quarter)}
as TimeSeries,
[Measures].[Store Sales],
Rank(Time.CurrentMember, TimeSeries))'

Select
{[Measures].[Store Sales], [Measures].[SalesForecast]}
on columns,
{[Time].[1997].[Q3].[7]:[Time].[1997].[Q3].[9]}
on rows
From Sales

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks you for your reply. What you sent me looks like a SQL statment
that would be used in Enterprise Manager, rather than Analysis Services.
My Foodmart doesn't have a a Measure called SalesForecast, but I might
be able to find a way to use it. I have been working with LAG(12) and
LAG (24). This has made it possible to do the math that I was
interested in calculating.

Thanks,
TC



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I'm not sure what you mean by a SQL statement - what I sent is an
Analysis Services MDX query, that you can run against Foodmart in the
MDX Sample Application (as I did). The [SalesForecast] calculated
measure is defined and used within the scope of that MDX query, so you
should not expect to find it in the Foodmart database.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Well, I don't know. Trying to create a calculated member in Analysis
Manger with statments such as "With Member", "AS", and "Select" doesn't
work for me.

For now, below is what I am using, until I can understand the
"LinRegPoint" command. There are no "Select" statements in this MDX,
and it works in Analysis Manger. This is not based on Foodmart. I tried
it in Foodmart, but it didn't look right. I think it is because there
needs to be more than 24 months. This basic formula works in my cube,
and it accounts for seasonal items. It drills up or down and slices. It
is not perfect, but it is working.

SHIPPED QUANTITY FORECAST

IIF((Time.CurrentMember.Lag (24),[Measures].[Shipped
Quantity])=0,NULL,(Time.CurrentMember.Lag (12),[Measures].[Shipped
Quantity])/(Time.CurrentMember.Lag (24),[Measures].[Shipped
Quantity])-1)*(Time.CurrentMember.Lag (12),[Measures].[Shipped
Quantity])+(Time.CurrentMember.Lag (12),[Measures].[Shipped Quantity])

If you or anybody can help me understand LinRegPoint, I will be
greatful.

Thanks,
TC


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The "With Member", "AS", and "Select" should be stripped away when
defining a Calculated Member in Analysis Manager - they are part of the
complete MDX Query syntax.

Anyway, looking at your MDX expression, LinRegPoint() would not be
equivalent, since it does a Linear Regression extrapolation, whereas the
expression is exponential. You can use ParallelPeriod() instead of
Lag(), if you need forecasts at various time levels (Day/Month/Quarter):

SHIPPED QUANTITY FORECAST
[Measures].[Shipped Quantity])=0,NULL,
(ParallelPeriod(Year),
[Measures].[Shipped Quantity])
/(ParallelPeriod(Year,2),
[Measures].[Shipped Quantity])-1)
*(ParallelPeriod(Year),
[Measures].[Shipped Quantity])
+(ParallelPeriod(Year),
[Measures].[Shipped Quantity])

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote


I want to calculate forecasts the following way:

Eventhough my overall data is reported from Period
001.2004 to 012.2004 I want my forecasts calculated
key figure to start from period 007.2004. Again
I want this to be sort of like a rolling base period
meaning if my current month is 007.2004 the forecast
calculated measures must start from 008.2004 not earlier

Can you please share your views how I can implement this
kind of flexible forecast calculated measures.

Thanks
Simon

Yes. That produced indentical results, and it is alittle easier to read.

TC



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

When you say the current month is 007.2004, does that mean data is
loaded upto 007.2004; so the last loaded month is the "current" month?
Also, what forecast formula is used?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

Hi Deepak

I will explain it in a little more detail:

Assuming the current month is 004.2003 I want the forecast sales to be
computed only for the period beginning 005.2004 onwards.

The formula for forecast sales is as follows:

Forecast Sales = Planned Production for current period +
Stock in transit for prior period -
Total Forecast for current period +
Forecast Sales for prior period (please note I
referring the
forecast sales being computed for prior period is
added to the
current period forecast sales)


Many thanks if you could kindly clarify the MDX how I can achieve
this.

Thanks
Simon