locked
multiplying sets RRS feed

  • Question

  • I have a problem with multiplying two sets.  Here is my query:

     

    With 
    set [Sales] as
    crossjoin ([Account].[Total Net Sales], [Scenario].[act/fcst], [fiscal years].[Y2010])
    set [Pricing] as
    crossjoin ([account].[income expense type].[general sales drivers].[DR0000-PRicing], Scenario.budgetver1, [fiscal years].[Y2011])

    member [Calc] as
    '[Pricing]*[Sales]'

    select {[Calc]} on 0,
    {

    [Calendar].[Fiscal Month Name].members
    }
    on 1
    from westlake_budget where ([Organization].[Store 001 - Moberly])

     

    Here is the result:

                    Calc

    January                #Error

    February              #Error

    March   #Error

    April      #Error

    May       #Error

    June      #Error

    July        #Error

    August  #Error

    September         #Error

    October               #Error

    November          #Error

    December           #Error

     

    Does any one have suggestions on what i am doing wrong?

     

    Tuesday, August 24, 2010 6:46 PM

Answers

  • whats the name of your base-measure?
    - www.pmOne.com -
    • Marked as answer by homeguards Wednesday, August 25, 2010 2:52 PM
    Wednesday, August 25, 2010 2:23 PM
    Answerer
  • Thanks for the tip, I got it now:

     

    with

          member [Measures].[Sales] as

           ([Account].[Total Net Sales], [Scenario].[act/fcst], 

            [fiscal years].[Y2010], [Amount])

          member [Measures].[Pricing] as

           ([account].[income expense type].[general sales drivers].[DR0000-PRicing],

           Scenario.budgetver1, [fiscal years].[Y2011], [Amount])

          member [Measures].[Results] as

          (Sales * Pricing)

    select  

    {Sales,

    Pricing,

    Results

    } on 0,

    [Calendar].[Fiscal Month Name].members

    on 1

    from westlake_budget where ([Organization].[Store 001 - Moberly])

     

    • Marked as answer by homeguards Wednesday, August 25, 2010 2:52 PM
    Wednesday, August 25, 2010 2:51 PM

All replies

  • Hi,

    It would give you an error stating that 'Measure hierarchy Or Fiscal hierarchy is used more than once ' in the cross join.

    If you can re-formulate ur mdx something like this

    select {[Account].[Total Net Sales]} *
           {[account].[income expense type].[general sales drivers].[DR0000-PRicing]} *
    {[Scenario].[act/fcst], Scenario.budgetver1}
    on 1,
    {
    [fiscal years].[Y2010],[fiscal years].[Y2011]
    }
    on 0
    from westlake_budget where ([Organization].[Store 001 - Moberly])

     

    Regards

     Gokul

    Wednesday, August 25, 2010 1:34 AM
  • first of all, your calculations "Sales" and "Pricing" should not be defined as sets, they are tuples and should be created in the measures dimension without using CrossJoin-function

    then you can simply multiply their results using "*"
    if you define them as sets the engines crossjoins the two sets becuase "*" is also the operator for crossjoin if used for sets as in your case

    try this query:

    WITH
    MEMBER [Measures].[Sales] as (
    [Account].[Total Net Sales], 
    [Scenario].[act/fcst], 
    [fiscal years].[Y2010]
    )
    
    MEMBER [Measures].[Pricing] as (
    [account].[income expense type].[general sales drivers].[DR0000-PRicing], 
    Scenario.budgetver1, 
    [fiscal years].[Y2011]
    ) 
     
    MEMBER [Measures].[Calc] as (
    [Measures].[Pricing]*[Measures].[Sales]
    )
    
    SELECT {[Measures].[Calc]} on 0,
    {[Calendar].[Fiscal Month Name].members} on 1
    FROM westlake_budget 
    WHERE (
    [Organization].[Store 001 - Moberly]
    )

    hth,
    gerhard

     


    - www.pmOne.com -
    Wednesday, August 25, 2010 7:36 AM
    Answerer
  • I didnt get  any of the queries to supply the correct results.  all i get back is nulls.

     

    With

          set [Sales] as

          crossjoin ([Account].[Total Net Sales], [Scenario].[act/fcst], [fiscal years].[Y2010])

          set [Pricing] as

          crossjoin ([account].[income expense type].[general sales drivers].[DR0000-PRicing], Scenario.budgetver1, [fiscal years].[Y2011])   

    select [Calendar].[Fiscal Month Name].members on 0,

    {

    Sales,

    Pricing

    }

    on 1

    from westlake_budget where ([Organization].[Store 001 - Moberly])

     

    All I need to do is multiply Sales and Pricing.

    Wednesday, August 25, 2010 1:50 PM
  • whats the name of your base-measure?
    - www.pmOne.com -
    • Marked as answer by homeguards Wednesday, August 25, 2010 2:52 PM
    Wednesday, August 25, 2010 2:23 PM
    Answerer
  • whats the name of your base-measure?
    - www.pmOne.com -

    If I understand correctly, it would be stored in a measure called Budget Amount.
    Wednesday, August 25, 2010 2:45 PM
  • Thanks for the tip, I got it now:

     

    with

          member [Measures].[Sales] as

           ([Account].[Total Net Sales], [Scenario].[act/fcst], 

            [fiscal years].[Y2010], [Amount])

          member [Measures].[Pricing] as

           ([account].[income expense type].[general sales drivers].[DR0000-PRicing],

           Scenario.budgetver1, [fiscal years].[Y2011], [Amount])

          member [Measures].[Results] as

          (Sales * Pricing)

    select  

    {Sales,

    Pricing,

    Results

    } on 0,

    [Calendar].[Fiscal Month Name].members

    on 1

    from westlake_budget where ([Organization].[Store 001 - Moberly])

     

    • Marked as answer by homeguards Wednesday, August 25, 2010 2:52 PM
    Wednesday, August 25, 2010 2:51 PM