Re: Using 2 fact tables in AS2005

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Marius,

Thanks for your reply; I have been trying out what you sent the last few 
days as I am still currently learning MDX.  I think I should be a little 
clearer about what I am trying to do.  As well as being a dimension that 
specifies the granularity of the Forecasts, ReportMonth is also a marker of a 
point in time.  So if ReportMonth is selected for a particular month then it 
should show the situtation for Actuals and Forecast *as at that month in 
time*.  It's simplest if i describe Actuals (in non-MDX):

For Actuals:
Where Month <= ReportMonth then Actuals else 0
so for 2005 06 as reportmonth, actuals should appear for all months up to 
2005 06 *even if the actuals measure has values for later months*.

For ActualsandForecast:
Where Month <= ReportMonth then Actuals else Forecast
so for 2005 06 as reportmonth the actuals for all months to 2005 06 will be 
shown along with the forecast values entered *at the point in time of 2005 
06* for all months after 2005 06.  

I hope that makes it a bit clearer and appreciate any further assistance you 
can give.

Regards,

Rob



"Marius Dumitru" wrote:

> I understand the intention is that, for a given Month, to find whether there 
> is at least one Forecast table row and, if so, take the most recent forecast 
> (i.e. the one for the most recent ReportMonth).
> 
> I guess this can be expressed as:
> Tail(
>         {
>             ([Month].[Month].CurrentMember, [Measures].[Actuals]),
>             NonEmptyCrossjoin(
>                 Filter(
>                     [Report Month].[Month].Members,
>                     [Month].[Month].Properties("Name") > [Report 
> Month].[Month].Properties("Name")    //not sure if the Filter is really 
> necessary in your scenario
>                 ),
>                 [Measures].[Value]
>             )
>     }
> ).Item(0).Value
> 
> (or use this as a starting point for fine tuning)
> 
> -- 
> Hope this helps
> 
> Marius
> 
> 
> "Robert Chapman" <RobertChapman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message 
> news:1381F73A-004F-49FD-84A0-6505F44119E0@xxxxxxxxxxxxxxxx
> > Hi,
> >
> > I am trying to come to grips with AS2005's ability to have two fact tables
> > in a cube and to create calculated measures that work successfully.
> >
> > For background, the two fact tables are:
> > Fact table 1 stores Actuals financial data - fields being Month, 
> > AccountID,
> > CountryID, FunctionID and Value.
> > Fact table 2 stores Forecast financial data - fields being the same as for
> > Actuals plus a ReportMonth field - the month in which the forecast was 
> > done.
> >
> > The Forecast table only contains *future* values so the cube needs to
> > calculate an ActualsPlusForecast measure equal to:
> > Actuals values up to the ReportMonth + Forecast values from after
> > ReportMonth.
> >
> > This calculated measure so far is:
> > Iif([Month].[Month].CurrentMember.Properties("Name") > [Report
> > Month].[Month].CurrentMember.Properties("Name"),[Measures].[Value],[Measures].[Actuals])
> >
> > However I've discovered that Month is coming through as '2005 12' but
> > ReportMonth is always 'All' therefore the comparison fails in its 
> > intention.
> > I would guess the problem is at the data source level, in that the link
> > between the two fact tables is incorrect.  I need to do what in relational
> > terms would be a crossjoin so that Actuals values are available for all
> > ReportMonths so that in the calculated formula above ReportMonth will 
> > bring
> > back an actual month name.  Can anyone suggest how this might be done?,
> > nothing I have tried seems to work.
> >
> > Many thanks,
> >
> > Rob 
> 
> 
> 
.

Relevant Pages

  • Using 2 fact tables in AS2005
    ... Actuals plus a ReportMonth field - the month in which the forecast was done. ... The Forecast table only contains *future* values so the cube needs to ...
    (microsoft.public.sqlserver.olap)
  • Re: Using 2 fact tables in AS2005
    ... I understand the intention is that, for a given Month, to find whether there ... is at least one Forecast table row and, if so, take the most recent forecast ... (i.e. the one for the most recent ReportMonth). ... > Fact table 1 stores Actuals financial data - fields being Month, ...
    (microsoft.public.sqlserver.olap)
  • RE: IIf Statement in "Update To" field of Update Query
    ... In essence, The Actuals table only has Jan Actual, and no "Jan". ... "KARL DEWEY" wrote: ... are working with in the query. ... Below I am assuming the table names to be Actuals and Forecast. ...
    (microsoft.public.access.queries)
  • Re: Zero Values on Charts
    ... I am currently developing a spreadsheet which will allow the Actuals, ... Forecast and Budgets of a financial year to be viewed. ... so that they can be viewed against budget and forecast. ... Does anyone know how you can have a line graph that looks at a range ...
    (microsoft.public.excel.charting)
  • Zero Values on Charts
    ... I am currently developing a spreadsheet which will allow the Actuals, ... Forecast and Budgets of a financial year to be viewed. ... so that they can be viewed against budget and forecast. ... Does anyone know how you can have a line graph that looks at a range ...
    (microsoft.public.excel.charting)