Post reply

Trying to write MDX statement to do a custom running total

  • August 30, 2011 at 9:59 am

    #243114

    Here's where I am

    WITH MEMBER Measures.[New Incident Running Total] AS 'SUM([Time of Day].[Half Hour].&[0]:[Time of Day].[Half Hour].CurrentMember,[Measures].[New Incidents])'

    MEMBER Measures.[DistinctCountUsers] AS 'DISTINCTCOUNT([Clients].[Client ID].Children)'

    MEMBER Measures.[Running User Count] AS '[Clients].[User Count].currentmember.member_value'

    SELECT

    { [Time of Day].[Half Hour].[Half Hour].ALLMEMBERS }

    ON COLUMNS,

    { [Measures].[New Incident Running Total],[Measures].[Running User Count],Measures.[DistinctCountUsers] }

    ON ROWS

    FROM [Support Dashboard]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR

    The idea was to get a running sum of user counts from customers that had a support incident.

    I.e. if Client XYZ calls in at 7:30am with a complaint, then their user total goes into the sum starting at 7:30am and remaining in the sum of users with problems all day. If Client XYZ calls back at 10:30am with another problem, well they already called once today so we don't count them. The idea is to get a running total of incidents per licensed user across a single day (the cube is purged of data at night and reloaded on a minute by minute basis during the day).

    Any ideas? I keep running the above mdx and getting the value of ALL for Measures.[Running User Count]


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • August 30, 2011 at 10:27 am

    #1375120

    My MDX is quite rusty, but the issue is that when you calculate your running total you are using currentmember for the time dimension when you want to be using everything from the beginning of the day to the currentmember.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 2 posts - 1 through 1 (of 1 total)

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