Forum Discussion

MSuser5's avatar
MSuser5
Helper III
2 years ago
Solved

MDX Query

Hi Team,

 

I would like to calculate sum of value by excluding 'null' values in the column, I have tried in SSAS cube-new MDX as like below but the sum result is wrong instead of 9845  result is : -5662 (coming as negative and wrong value)

my MDX (sum of wait time in minutes) : (sum([Measures].[Wait Time In Sec])/sum([Measures].[Total]))/60

 

kindly suggest or correct me where i missed .

 

Thanks,

 

Need Help
  • MSuser5's avatar
    MSuser5
    2 years ago

    Thanks cpwebb ,

     

    I have used SUM(coalesceEmpty([Measure].[waitTimeInSec],0)) and it's working fine.

9 Replies

  • cpwebb's avatar
    cpwebb
    Microsoft Employee
    2 years ago

    In MDX (I assume you're using SSAS Multidimensional) using the SUM function with a measure in the way you're doing here doesn't actually have any effect - the default aggregation behaviour of the measure will be used. So using the following MDX in a measure:

    ([Measures].[Wait Time In Sec]/[Measures].[Total])/60

    should give you the same incorrect result. What's more, MDX automatically ignores null values while aggregating anyway. Can you provide more details - with sample data - of what you want to do here?

     

    More complex aggregation behaviour is possible using SCOPE statements. The following blog post might help:

    https://blog.crossjoin.co.uk/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/

     

    Chris

     

    • MSuser5's avatar
      MSuser5
      Helper III
      2 years ago

      hi cpwebb ,

       

      yes i'm using SSAS multidimentional table here i tried to create new calculated measure like below 

       

      MDX (sum of wait time in minutes) : sum([Measures].[Wait Time In Sec])

      here is my table:

      Wait Time In SecTotal
      588965568
      566894869
      NULL9562
      963477856
      775464489
      NULL8965
      896477854

       

      Expected total  of waitTimeInSec = 379125

       

      i'm not sure may be due to NULL values result is coming : -266545 ( as negative)

       

      kindly suggest how to create measure with replacing NULL values as 0  and sum of total.

       

      Thanks,

      MS

      • cpwebb's avatar
        cpwebb
        Microsoft Employee
        2 years ago

        Is waitTimeInSec itself a calculated measure? If so, what is the definition? I don't think the nulls are the problem here.

         

        Chris

  • cpwebb's avatar
    cpwebb
    Microsoft Employee
    2 years ago

    I don't think the nulls are the problem - I don't see how they would result in a negative value here. What type of aggregation is your waitTimeInSec measure using? As I said, if you have already built a measure then writing something like sum([Measures].[Wait Time In Sec])  is redundant and [Measures].[Wait Time In Sec] should give you the same incorrect result.

    • MSuser5's avatar
      MSuser5
      Helper III
      2 years ago

      Hi cpwebb ,

       

      i too wondering about result eventhough [Wait Time In Sec] is int datatype and i created only one measure which is used sum function.

      sum([Measures].[Wait Time In Sec])

       

      Thanks,