locked
Projection in mdx query RRS feed

  • Question

  • Hello,

    I have a requirement where I need to project data point based on past data points. Like for 2015,  I have data points for Jan,Feb and March. For April, I need to do Yeartodate and then divide by Count of nonempty data points. Like Jan is 25, Feb  is 40 and mar is 50, then the projection is 115/3 for remaining 9 months of 2015. I need to count NONEMPTY Measure to get the count. Count has to be 3 for march. Since , there is no data after march, count for Apr to Dec should all have 3.So, the count should look like this:

    1 2 3 3 3 3 3 3... till Dec.

    I appreciate your help.

    Friday, May 15, 2015 2:14 PM

Answers

  • I don't think COUNT( DESCENDANTS(...)) will return the result you want and I'd avoid the use of FILTER where ever possible as it's iterative, so it will always be out performed by set based functions like NONEMPTY.

    The way I'd do this would be something like the following:

    COUNT( NONEMPTY( PERIODSTODATE( [Date].[Calendar].[Month] ), [Measures].[My Measure] ) )


    http://darren.gosbell.com - please mark correct answers

    Saturday, May 16, 2015 9:21 PM

All replies

  • This is the calculation you need -

    count(filter ( DESCENDANTS( [Date of Service].[Calendar].CurrentMember, [Date of Service].[Calendar].[Month In Year Number] ), [Measures].[Agent Call Count] > 0 ))

    Here is the query using that calculation. It is against my cube here, but it should give you the idea how to come up with calculation for your cube.

    with member measures.total 
    as count(filter ( DESCENDANTS( [Date of Service].[Calendar].CurrentMember, [Date of Service].[Calendar].[Month In Year Number] ), [Measures].[Agent Call Count] > 0 ))
    select measures.total on 0
    from asdw where ([Date of Service].[Year Number].&[2015])

    Friday, May 15, 2015 6:54 PM
  • I don't think COUNT( DESCENDANTS(...)) will return the result you want and I'd avoid the use of FILTER where ever possible as it's iterative, so it will always be out performed by set based functions like NONEMPTY.

    The way I'd do this would be something like the following:

    COUNT( NONEMPTY( PERIODSTODATE( [Date].[Calendar].[Month] ), [Measures].[My Measure] ) )


    http://darren.gosbell.com - please mark correct answers

    Saturday, May 16, 2015 9:21 PM