Post reply

How to do: dimension * measure

  • November 18, 2004 at 4:37 am

    #161938

    Hi,

    I am new to MDX querying .

    We have a cube with the following measures:

    • Activations
    • Cancelarions
    • Terminations

    Associated with contracts. But each contract can have a quantity associated (dimension).

    We need to create a calculated member that would return:

    • quantity associated * [measure].[activations].
    • quantity associated * [measure].[cancelations].
    • quantity associated * [measure].[terminations].

    Of course that only the quantity associated with an activation (et al.) must be considered.

    We could solve this in the relational source, but we rather do it using MDX.

    We have tryied to use quantity associated as a mesaure but it does not restrict the quantity associated with an activation (et al.).

    Thanks in advance.

  • November 18, 2004 at 2:37 pm

    #530736

    I am new too Im afraid.

    Have you looked at the member properties of dimensions. It is in books online:

    example:

    WITH

    MEMBER [Measures].[Store Size] AS

    'Val(Store.CurrentMember.Properties("Store Sqft"))'

    SELECT

    {[Measures].[Unit Sales], [Measures].[Store Size]} ON COLUMNS,

    {[Store].[Store Name].Members} ON ROWS

    From Sales

    You could multiply the property by a measure. Problem is that Im not sure what value you get when you are not using the dimension that has the property. You could also try <>.currentmember.datamember.Property("quantity associated")

  • November 18, 2004 at 8:29 pm

    #530757

    I'm not sure I fully understand why you wouldn't have the 'quantity associated' as a measure (which would obviosuly make the entire problem a lot easier to solve), but leaving it as a dimension, and assuming the members are numbers (eg 1, 2, 3... 9, 10), then you could try the following:

    IIF([Quantity Associated].CurrentMember.Level > 1, CInt([Quantity Associated].CurrentMember.Name) * [Measures].CurrentMember, 0)

    Assuming you have only the three measures (activiations, cancellations and terms) then this would (should?) return the product of the currently selected measure and the currently selected 'quantity associated'.  You should check BOL to ensure that the level # specified is right, I can't rememember whether level 1 or level 0 is the all member.  This also assumes that the 'quantity associated' dimension has only 1 true level (ie only 1 level *other* than the all level).  Also, instead of zero'ing the result if there is no 'quantity associated' member selected, you could put in another figure, but using the method below would work a lot better.

    As I started out with, if the 'quantity associated' is really a contract count, wouldn't it make more sense to have a 'Contract Count' (or 'Quantity of Contracts') measure, and then just multiply measure1 * measure 2.  You could still put this into discrete buckets in a 'Quantity Associated' dimension if required.

     

    Steve.

  • November 19, 2004 at 3:16 am

    #530775

    Thanks for your replies.

    Steve,

    The one contract can have from 1 upto 200 slots associated (being the slot the 'quantity associated').

    We were able to find another solution after some tentatives. The solution we have found is:

    Sum

    (Descendants([Num of Slots].CurrentMember, [Num of Slots].[Num Of Slots]), StrToValue([Num of Slots].CurrentMember.Name)*[Measures].[Activations])

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply