locked
parallel period not working for week 53 RRS feed

  • Question

  • Hi All,

    I am using the below code to get the last years week 53. Since this year has only 52 when I use nextmember its giving me 1st week of 2015. Please let me know how to get the last years last week with this

    SCOPE ([Time Calculations Week Hierarchy].[Time Calculation Week Hierarchy Name].[Prior Year]
    ,Descendants([Date].[Week Hierarchy].[2015], [Date].[Week Hierarchy].[Week Name]));       // only FY 2015 week members

        THIS =  ([Time Calculations Week Hierarchy].[Time Calculation Week Hierarchy Name].[Current] 
    ,ParallelPeriod([Date].[Week Hierarchy].[2015], 1, [Date].[Week Hierarchy].currentmember.nextmember)); 
    END SCOPE; 

                             

    p

    Monday, November 23, 2015 9:14 PM

Answers

  • Hi Pravin.c,

    According to your description, you want to use parallelperiod() to get the last week of previous year. Right?

    In MDX, since we have built the hierarchy "Year->Week", we can just use closingperiod() to get the last week of a year without determining how many weeks a year has. Please refer to MDX below:

    select {} on 0,
    closingperiod([Date].[Calendar Weeks].[Calendar Week],
    parallelperiod([Date].[Calendar Weeks].[Calendar Year],1,
    [Date].[Calendar Weeks].[Calendar Year].&[2014])) on 1
    from
    [Adventure Works]

    Reference:
    ClosingPeriod (MDX)

    Regards,


    Simon Hou
    TechNet Community Support


    Tuesday, November 24, 2015 11:45 AM

All replies

  • Simplifing the above version to 

    SELECT  { [Measures].[sales]}  On 0,
    {
    ParallelPeriod([Date].[Week Hierarchy].[Year Number], 1, ([Date].[Week Hierarchy].[Week Name].&[522].nextmember))

    } ON 1
    FROM CUBE

    -- &[522] referring to 52nd week this year since I am going back to last year using parallelperiod when I use nextmember its giving 2015 1st week instead of 2014 53rd week.


    p

    Monday, November 23, 2015 9:38 PM
  • Hi P,

    It really depends on which week you want. With ParallelPeriod going back one year, you will go to the same week number in the preceding year. If you are on week 53 (only some years have 53 weeks) you might not get a week in the preceding year. And week 53 will tend not to be referenced by other ParallelPeriods.

    Something you might consider (I can't say that it is correct, as it depends on what you want) is to go back 52 weeks. This way, it won't matter which week you are on, you will always get a valid week "one year ago". Going back 52 weeks can be achieved with the .lag(52) function. ie [Date].[Week Hierarchy].lag(52)

    A similar problem is also found when looking for the same week in the previous period. Most company reporting is on periods that contain exactly 4 or 5 full weeks. So, getting the same week in the preceding period when you are on week 5 is problematic. Again, you might simply go back 4 weeks, but it really depends on what the financial requirements are.

    Note, you can always use a CASE statement to determine which week of the year you are in and do something different on week 53.


    Richard

    Tuesday, November 24, 2015 4:16 AM
  • Hi Pravin.c,

    According to your description, you want to use parallelperiod() to get the last week of previous year. Right?

    In MDX, since we have built the hierarchy "Year->Week", we can just use closingperiod() to get the last week of a year without determining how many weeks a year has. Please refer to MDX below:

    select {} on 0,
    closingperiod([Date].[Calendar Weeks].[Calendar Week],
    parallelperiod([Date].[Calendar Weeks].[Calendar Year],1,
    [Date].[Calendar Weeks].[Calendar Year].&[2014])) on 1
    from
    [Adventure Works]

    Reference:
    ClosingPeriod (MDX)

    Regards,


    Simon Hou
    TechNet Community Support


    Tuesday, November 24, 2015 11:45 AM