Search code examples
mdxiccube

Addition and substraction Ok, but not Division


using following Calc members :

create calculated member [NbJoursNonAmbuTemp] as 
   sum([Ambu].[Ambu].[Ambu].[Non Ambulatoire], [Nombre de Journées])
create calculated member [NbJoursNonAmbuFinal] as 
    IIF( [Ambu].[Ambu].current is [Ambu].[Ambu].[Ambulatoire], 0, divN([NbJoursNonAmbuTemp], 1 , null)   )
create calculated member [NbSejoursNonAmbuTemp] as 
    sum([Ambu].[Ambu].[Ambu].[Non Ambulatoire], [NbSejours])
create calculated member [NbSejoursNonAmbuFinal] as 
    IIF( [Ambu].[Ambu].current is [Ambu].[Ambu].[Ambulatoire], 0, divN([NbSejoursNonAmbuTemp], 1 , null)   )

The following MDX :

WITH 
  MEMBER toto AS 
    Sum
    (
      CrossJoin
      (
        Descendants
        (
          [Classification GHM].[DA_GP_GA_GHM].CurrentMember
         ,[Classification GHM].[DA_GP_GA_GHM].[GHM]
         ,SELF
        )
       ,Descendants
        (
          [Periode].[Periode].CurrentMember
         ,[Periode].[Periode].[Année-L]
         ,SELF
        )
      )
     ,
        [Measures].[NbSejoursNonAmbuFinal] * [Measures].[dsmFrance]
      / 
        [Measures].[NbJoursNonAmbuFinal]
    ) 
SELECT 
  {
    toto
   ,[Measures].[NbJoursNonAmbuFinal]
  } ON 0
 ,[Classification GHM].[DA_GP_GA_GHM].[All-M] ON 1
FROM [Cube]
WHERE 
    {
      [Etablissement].[Lieu établissement].[Etablissement-L].&[720016724]
    }
  * 
    {[Periode].[Periode].[Année-L].[2014]};

Does not give the expected value for toto...

But if instead of (Division)

,([Measures].[NbSejoursNonAmbuFinal] * [Measures].[dsmFrance]) / [Measures].[NbJoursNonAmbuFinal]

I try (Addition)

,([Measures].[NbSejoursNonAmbuFinal] * [Measures].[dsmFrance]) + [Measures].[NbJoursNonAmbuFinal]

or (Substraction)

,([Measures].[NbSejoursNonAmbuFinal] * [Measures].[dsmFrance]) - [Measures].[NbJoursNonAmbuFinal]

everything is calculated correctly...

What Am I doing wrong?

Txs to @whytheq suggestion, I managed having the good result using :

WITH 
  MEMBER HospidiagWIP AS 
    Sum
    (
      CrossJoin
      (
        Descendants
        (
          [Classification GHM].[DA_GP_GA_GHM].CurrentMember
         ,[Classification GHM].[DA_GP_GA_GHM].[GHM]
         ,SELF
        )
       ,Descendants
        (
          [Periode].[Periode].CurrentMember
         ,[Periode].[Periode].[Année-L]
         ,SELF
        )
      )
     ,
      [Measures].[NbSejoursNonAmbuFinal] * [Measures].[dsmFrance]
    ) 
    MEMBER [Measures].[IPDMS HospidiagTest] as HospidiagWIP / [Measures].[NbJoursNonAmbuFinal]  , format_string='0.000'
SELECT 
  {
   [Measures].[IPDMS HospidiagTest]
   ,[Measures].[IPDMS]
  } ON 0
 ,[Classification GHM].[DA_GP_GA_GHM].[All-M] ON 1
 // ,{[Classification GHM].[DA_GP_GA_GHM].[All-M]} * {[Periode].[Periode].[Année-L].[2013], [Periode].[Periode].[Année-L].[2014]} ON 1

FROM [Cube]
WHERE 
    {
      [Etablissement].[Lieu établissement].[Etablissement-L].&[720016724]
    }
  * 
    {[Periode].[Periode].[Année-L].[2014]}

Solution

  • Try the following and inspect the results - does it help analyse what the engine is doing?

    Try enforcing a solve order.

    WITH 
      MEMBER PREtoto AS
            [Measures].[NbSejoursNonAmbuFinal] * [Measures].[dsmFrance]
          / 
            [Measures].[NbJoursNonAmbuFinal]
         ,SOLVE_ORDER = 1
      MEMBER toto AS 
        Sum
        (
          CrossJoin
          (
            Descendants
            (
              [Classification GHM].[DA_GP_GA_GHM].CurrentMember
             ,[Classification GHM].[DA_GP_GA_GHM].[GHM]
             ,SELF
            )
           ,Descendants
            (
              [Periode].[Periode].CurrentMember
             ,[Periode].[Periode].[Année-L]
             ,SELF
            )
          )
         ,
          PREtoto
        ) 
        ,SOLVE_ORDER = 2   //<<or try the other way by changing to -1
    SELECT 
      {
        PREtoto
       ,toto
       ,[Measures].[NbJoursNonAmbuFinal]
      } ON 0
     ,[Classification GHM].[DA_GP_GA_GHM].[All-M] ON 1
    FROM [Cube]
    WHERE 
        {
          [Etablissement].[Lieu établissement].[Etablissement-L].&[720016724]
        }
      * 
        {[Periode].[Periode].[Année-L].[2014]};