locked
ParallelPeriod equivalent for a time RANGE? RRS feed

  • Question

  • Hi,

    Our client wants to report on their trade volume for last year as compared to the current quarter. For simplicity let's pretend they have a report where they have a two key measures:

    [Trade Volume - Tons]

    [Trade Volume - Tons MTD]

    [Trade Volume - Tons] is based on outlook - that is, for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until the current period, and forecast for the current and future periods.

    [Trade Volume - Tons MTD] is based only on actuals - that is for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until and including the current period, and 0 for any future periods.

    If Feb09 is our current period, and we are using quarter on the time dimension (where quarter 1=Jan09,Feb09,Mar09) and we have the following data:

    Jan09 Trade Volume Actual: 100 Trade Volume Forecast: 150

    Feb09 Trade Volume Actual: 50 Trade Volume Forecast: 200

    March09 Trade Volume Actual: 75 Trade Volume Forecast: 225

    Then

    [Trade Volume - Tons]=100+200+225=525

    [Trade Volume - Tons MTD]=100+50=150

    This is a problem, because the comparison with their current results ([Trade Volume - Tons MTD]) with what they 'forecast' ([Trade Volume - Tons]) is not based on the same period of time - we are comparing the sum of two periods versus three periods.

    To solve this we changed the reporting period to be monthly granularity, and now select Jan09-Feb09 as our range (as opposed to having a quarter granularity and selecting Q1,2009 in the example above).

    This works well and produces the expected results:

    [Trade Volume - Tons]=100+200=350

    [Trade Volume - Tons MTD]=100+50=150

    However, this introduces a secondary problem: we are doing a prior year calculation on the Trade Volume also, so the users can compare how the actuals are comparing to the same period last year.

    To do this we use the following formula for the prior year calculation:

    Prior Year Actuals=([Measures].[Trade Volume - Tons], ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember))

    The problem is as soon as we move from quarter granularity to (monthly granularity AND select more than one monthly period) the Prior Year Actuals calculation produces a an error "The MDX function CURRENTMEMBER failed because the coordinate for the 'Period Year' attribute contains a set".

    So, ParallelPeriod does not like it when currentmember is a range (Jan09,Feb09) rather than a single period (Jan09).

    Any suggestions on how I can get around this?

    Tuesday, January 27, 2009 3:52 AM

Answers

  • Hi,

    Darren's idea is great, just need's a little modification (orange part below). Try this MDX on Adventure works:

    with member x as  
     Sum(EXISTING [Date].[Calendar].[Month].MEMBERS,  
     ( ParallelPeriod( [Date].[Calendar].[Calendar Year],  
                       1,  
                       [Date].[Calendar].currentmember  
                     )  
     ,[Measures].[Sales Amount]  
     )  

     )
    FORMAT_STRING = '#,##0.00' 

    select  
     x on 0  
    from  
    [Adventure Works]  
    where  
    (  
    {[Date].[Calendar].[Month].&[2004]&[1]  
    ,[Date].[Calendar].[Month].&[2004]&[2]  
    }  

    You see, Generate destroys current context in that situation. You should do a sum over existing set (that's a good idea), while in the second part of the sum you provide a calculation you need (a tuple that jumps one year back and grabs your measure's value). No, need to loop inside a sum (sum is a kind of a loop itself). Doing two loops inside each other over the same index (currentmember) losses that index.

    To conclude, use this calculation in your case:

    SUM( EXISTING [Time].[544 Hierarchy].[Period Month].Members,
            (
             
    ParallelPeriod( [Time].[544 Hierarchy].[Period Year], 1, [Time].[544 Hierarchy].currentmember ),
             
    [Measures].[Trade Volume - Tons]
            )
          )

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    • Marked as answer by troyh78 Wednesday, January 28, 2009 12:07 AM
    Tuesday, January 27, 2009 2:15 PM
    Answerer

All replies

  • Unfortunately, dealing with multiple members in a filter is not always easy. Below is one option that might work for you. It uses the EXISTING operator to get the set of months that relate to the current selection on the [Time].[544 Hierarchy] so this should work for multiple months and for levels above months like quarters. Then I use Generate() to effectively loop over the set of members and do a ParallelPeriod() on each one.

    Prior Year Actuals= SUM( 
        GENERATE(
            EXISTING [Time].[544 Hierarchy].[Month].Members
            , ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember)
        
    )
    ,[Measures].[Trade Volume - Tons])


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Tuesday, January 27, 2009 4:58 AM
  • Thanks for the suggestion Darren,

    I modifed your formula to rename the month attribute of the time hierarchy (mine's called Period Month), but unfortunately it does not return any values. In SSAS in the cube browser I drag my 544 Hierarchy.Period Month field into the report filter, and then the newly created calculated measure called [PY Trade Volume - Tons] into the detail section but I get "#VALUE!" returned if I filter on a single month (Jan09), or multiple months (Jan09,Feb09).

    (SUM(GENERATE(EXISTING [Time].[544 Hierarchy].[Period Month].Members,ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember)),[Measures].[Trade Volume - Tons]))

    But I don't know enough about what you're doing to know why I'm not getting any data returned. When I follow the same procedure described above for the original formula:

    ([Measures].[Trade Volume - Tons], ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember))

    I do get a result when filtering on Jan09, but "#VALUE!" returned when I filter on Jan09 & Feb09.

    I would appreciate any further comments or ideas!

    Tuesday, January 27, 2009 7:11 AM
  • Hi,

    Darren's idea is great, just need's a little modification (orange part below). Try this MDX on Adventure works:

    with member x as  
     Sum(EXISTING [Date].[Calendar].[Month].MEMBERS,  
     ( ParallelPeriod( [Date].[Calendar].[Calendar Year],  
                       1,  
                       [Date].[Calendar].currentmember  
                     )  
     ,[Measures].[Sales Amount]  
     )  

     )
    FORMAT_STRING = '#,##0.00' 

    select  
     x on 0  
    from  
    [Adventure Works]  
    where  
    (  
    {[Date].[Calendar].[Month].&[2004]&[1]  
    ,[Date].[Calendar].[Month].&[2004]&[2]  
    }  

    You see, Generate destroys current context in that situation. You should do a sum over existing set (that's a good idea), while in the second part of the sum you provide a calculation you need (a tuple that jumps one year back and grabs your measure's value). No, need to loop inside a sum (sum is a kind of a loop itself). Doing two loops inside each other over the same index (currentmember) losses that index.

    To conclude, use this calculation in your case:

    SUM( EXISTING [Time].[544 Hierarchy].[Period Month].Members,
            (
             
    ParallelPeriod( [Time].[544 Hierarchy].[Period Year], 1, [Time].[544 Hierarchy].currentmember ),
             
    [Measures].[Trade Volume - Tons]
            )
          )

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    • Marked as answer by troyh78 Wednesday, January 28, 2009 12:07 AM
    Tuesday, January 27, 2009 2:15 PM
    Answerer
  • Thanks Tomislav, I did over complicating that a bit didn't I :)

    But I think we still have an issue, as if you use this code with Excel 2007 it will not work as Excel 2007 uses sub-selects for filtering mulitple members.

    I was thinking that an even easier approach might be to teach the users to just pick a single month and then use the QTD(), YTD() or PeriodsToDate() functions to create the appropriate measures.

    CREATE MEMBER CURRENTCUBE.Measures.[QTD Trade Volume - Tons] as
     
    SUMQTD( [Time].[544 Hierarchy].CurrentMember ),
              [Measures].[Trade Volume - Tons]
          )

    CREATE MEMBER CURRENTCUBE.Measures.[LY QTD Trade Volume - Tons] as
    SUM
    ( QTD(
              ParallelPeriod( [Time].[544 Hierarchy].[Period Year], 1, [Time].[544 Hierarchy].currentmember )
            )
    ,
              [Measures].[Trade Volume - Tons]
          )
         

    So if the levels in the date hierarchy have the approriate types the user then only has to pick Feb 2009 and the "QTD" and "LY QTD" measures would figure out the correct range. This would make it simpler for the users and would avoid potential issues with sub-selects.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Tuesday, January 27, 2009 10:42 PM
  • Thanks Tomislav and Darren! The measure is now reporting correctly when selecting single and multple date ranges using Tomislav's formula. Luckily the users use a third party tool to do the selects and they are coming out ok. They won't use excel to connect directly to the cube.

    We will go with this for time being, but I'll also test Darrens solution about editing the base measure - this would be preferable because it avoids the users having to change their month selection every month, and we can move back to using the current quarter as the time selection.

    Thanks so much for your help guys and I will provide further feedback about using the QTD and YTD functions.

     

     

    Wednesday, January 28, 2009 12:12 AM
  • troyh78 said:

    We will go with this for time being, but I'll also test Darrens solution about editing the base measure - this would be preferable because it avoids the users having to change their month selection every month, and we can move back to using the current quarter as the time selection.



    Actually you would still need to pick a month when you use the QTD/YTD functions, it's just the users would only have to pick Feb 09 to get the QTD and YTD for Feb 09. They would not have to pick both Jan 09 and Feb 09. You can also do things like have a QTD later in the year (eg. May 09) that is Apr to May in a column next to a YTD that is the sum of Jan to May
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Wednesday, January 28, 2009 2:30 AM
  • Darren, I like your idea!

    I just bumped into this thread and saw a MDX I can correct. After reading it from the start I realized your last solution is the best one.

    Troyh78, you don't have to change anything but the name (maybe?) of the calculated measure(s) and their definition (for sure) according to Darren's advice. It's fast to implement and test. Just be sure to instruct your users, if they select more than one member, an error will occur. It wouldn't be a bug, it's by design in this case.

    The downside, if at all, is that the range is consecutive. Ehm? In my case, you can take any set of month members from hierarchy (in almost any client but Excel 2007, heh). Here, only one and it will give you MTD result, in every client. That's enough for a typical user. My experience teaches me that overdesign brings trouble to designer. KISS, as they say, maybe without the last S :-).

    As for months/quarter concern, you can make report's default (if you use any kind of reporting) to be current month, so the user really doesn't have to change anything and enjoys one-click report. Also, if the end user wants to see that MTD result for any of the previous months, fine, it's possible - all he has to do is select it from the param's list.

    If the mode of work is not reporting, but ad-hoc queries, users will have to select a month of course, just one, in slicer. Although that can be arranged too. One way is to make it a deafult member in hierarchy (I would avoid that because that would influence all other measures and reports). The other is to make it a default just for these measures - add one more iif(() to test whether user selected a month (currentmember is not All member) and providing current month in Darren's formula for true part. False part would be exact as Darren designed. Be sure to tell the users how it works.

    If your front-end supports that, you could use a calculated set that includes only one member - current month. That would be in a slicer, so that users don't have to change anything from month to month.

    As you see, there are many options.

    Additional YTD measures might be nice too.

    Regards to all,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

     

    Wednesday, January 28, 2009 2:35 PM
    Answerer
  • Hi everyone,

    I know this thread is quite old but I am facing a similar situation today.

    I use the formula to test my MDX code and it works find. I want to calculate, let´s say the Net Sales Last Year and then filter only the first and second quarter.

    If a put the Q1 and Q2 in the slicer (where clause of the MDX query) no problem at all.

    The problem is when the users using Excel try to do the same and put the Q1 and Q2 (for year 2015) in the pivot table filter. In this case the MDX code Excel generates uses as subselect to filter instead of a where clause. Then my formula does not work anymore, since the current member gets lost.

    Any ideas about how to make this works also in Excel?

    Any comment will be appreciated.

    Wednesday, December 2, 2015 10:56 AM