none
MDX calculation Expected value RRS feed

  • Question

  • Dear,

    This is my situation:
    We have salespersons that can have several clients, and according to the products they buy, clients can have multiple salespersons (M:N). Of course clients can switch over time from salesperson to salesperson. We know the current situation of which clients who currently belonging to which sales person even without buying anything (no fact) let us call that portfolio.

    What we would like to achieve is knowing for the current portfolio the expected value. Meaning that for example for salesperson x, I want to know the expected value (Reaff) belonging to the clients of salesperson x of the current period, but with values from these clients from the previous period.

    Also the dimensions salespersons and clients have SCD2.

    Here is my current situation and wanted result.

    So what I would like in for the Reaff value is the previous value of the client unrelated to the SalesPerson for the previous selected period.

    If anybody could help me out on this one?

    Thx

    Thursday, October 4, 2012 9:34 AM

Answers

  • Hello,

    I think it should be enough this:

    ([DimSalesPerson].[SalesPerson].[All],([DimPeriod].[Period].prevmember,[Measures].[Gross]))*[Measures].[Bridge portfolio Count]

    it retrieve the value for Gross in the previous period, for all SalesPersons (unrelated) and multiply it for "valid combinations" (Bridge portfolio count)

    HTH

    Bye


    Norman

    • Marked as answer by BramDe Thursday, October 4, 2012 2:51 PM
    Thursday, October 4, 2012 9:46 AM
  • Thanks Norman, the scope did not worked for me, but I figured out following calculation:

        IIF(ISEMPTY([Measures].[Vw Bridge Portfolio Count]), NULL,  
        ([Dim Sales Person].[Sales Person].[All],([Dim Time].[H_Date].CurrentMember.prevmember,[Measures].[Gross]))

    Thanks !!
    • Marked as answer by BramDe Thursday, October 4, 2012 2:51 PM
    Thursday, October 4, 2012 2:50 PM

All replies

  • Hello,

    I think it should be enough this:

    ([DimSalesPerson].[SalesPerson].[All],([DimPeriod].[Period].prevmember,[Measures].[Gross]))*[Measures].[Bridge portfolio Count]

    it retrieve the value for Gross in the previous period, for all SalesPersons (unrelated) and multiply it for "valid combinations" (Bridge portfolio count)

    HTH

    Bye


    Norman

    • Marked as answer by BramDe Thursday, October 4, 2012 2:51 PM
    Thursday, October 4, 2012 9:46 AM
  • Thanks Norman!

    Formula works and explenation is clear!!

    Will it also work for every level if we use a real Period Hierarchy like Year, Quarter, Month, Day?

    Thanks

    Thursday, October 4, 2012 10:57 AM
  • ([DimPeriod].[Period].prevmember,[Measures].[Gross]) retrieve the Gross in the previous period,

    ([DimSalesPerson].[SalesPerson].[All],([DimPeriod].[Period].prevmember,[Measures].[Gross])) retrieve the Gross in the previous period at total on Sales Person Dim (you say you want unrelate the Gross from SalesPerson)

    and after multiply the resilt by [Measures].[Bridge portfolio Count] to nullify the calculation were [Measures].[Bridge portfolio Count] is NULL.

    "Also if we have an aggreation and the Bridge Count is more than one, won't we get a wrong result?"

    Yes. to avoid this problem yoi should define a new empty measure (aggregation=SUM) in your DSV and then assign this SCOPE to the new measure

    SCOPE [Measures].[NewMeasures];

    SCOPE [DimSalesPerson].[SalesPerson].[SalesPerson];

    SCOPE [DimPeriod].[Period].[Period];

    SCOPE [DimClient].[Client].[Client];

    THIS=([DimSalesPerson].[SalesPerson].[All],([DimPeriod].[Period].prevmember,[Measures].[Gross]))*[Measures].[Bridge portfolio Count];

    END SCOPE;END SCOPE;END SCOPE;END SCOPE;

    Doing this the calculations is done at Client,SalesPerson,Period level and then aggregated by sum.Bye


    Norman

    Thursday, October 4, 2012 10:58 AM
  • Thanks Norman, the scope did not worked for me, but I figured out following calculation:

        IIF(ISEMPTY([Measures].[Vw Bridge Portfolio Count]), NULL,  
        ([Dim Sales Person].[Sales Person].[All],([Dim Time].[H_Date].CurrentMember.prevmember,[Measures].[Gross]))

    Thanks !!
    • Marked as answer by BramDe Thursday, October 4, 2012 2:51 PM
    Thursday, October 4, 2012 2:50 PM