locked
Running Total in MDX RRS feed

  • Question

  • I have a measure which is a percentage. This measure is on a daily basis, which rolls up to weeks, months, quarters, years etc.
    I need to compare this percentage to a threshold percentage which comes from a dimension and then report, the number of days the original percentage was within the threshold out of total number of days. How can I achieve this in MDX?

    Any help appreciated...

    Monday, April 25, 2011 4:30 AM

Answers

  • Hi,

    let's call

    - "Time hrc" the hierarchy Day->Weeks->Months->Quarters->Year defined on TIME DIM 

    - "Percentage" the measure defined on daily basis on which you want the calculation

    - "Threshold" the attribute which store values of the threshold, defined on THRESHOLD DIM

     

    Your formula should be something like (i assume you want to count the value below threshold):

    Sum
      (
        Descendants
        (
          [TIME DIM].[Time Hrc].CurrentMember
         ,[TIME DIM].[Time Hrc].[Day]
        )
       ,IIF
        (
            [Percentage]
          < 
            Val
            (
              [THRESHOLD DIM].[Threshold].CurrentMember
            )
         ,1
         ,null
        )
      )

      Count
      (
        Descendants
        (
          [TIME].[Time Hrc].CurrentMember
         ,[TIME].[Time Hrc].[Day]
        )
      )

     

    does it fits?

     

    bye


    Norman
    Wednesday, April 27, 2011 3:46 PM

All replies

  • Hi,

    let's call

    - "Time hrc" the hierarchy Day->Weeks->Months->Quarters->Year defined on TIME DIM 

    - "Percentage" the measure defined on daily basis on which you want the calculation

    - "Threshold" the attribute which store values of the threshold, defined on THRESHOLD DIM

     

    Your formula should be something like (i assume you want to count the value below threshold):

    Sum
      (
        Descendants
        (
          [TIME DIM].[Time Hrc].CurrentMember
         ,[TIME DIM].[Time Hrc].[Day]
        )
       ,IIF
        (
            [Percentage]
          < 
            Val
            (
              [THRESHOLD DIM].[Threshold].CurrentMember
            )
         ,1
         ,null
        )
      )

      Count
      (
        Descendants
        (
          [TIME].[Time Hrc].CurrentMember
         ,[TIME].[Time Hrc].[Day]
        )
      )

     

    does it fits?

     

    bye


    Norman
    Wednesday, April 27, 2011 3:46 PM
  • Thanks Norman, was perfect!
    Tuesday, May 3, 2011 8:53 AM