Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Using two levels from the same hierarchy on different axis for YTD

Answered Using two levels from the same hierarchy on different axis for YTD

  • Tuesday, February 19, 2013 6:33 PM
     
     
    Sign In to Vote
    0

    I have a Fiscal date hierarchy with two levels: Fiscal year and Fiscal month. I use this hierarchy in a year-to-date calculation using the PeriodsToDate function.

    I want to see the evolution of this measure for each fiscal month year after year.

                2010              2011                2012          
                          YTD                YTD                 YTD
    P1         100    100      125     125       240     240

    P2         125    225      110     235       150     390

    P3          90    315      140     375       120     510

    ...

    In this context, is there a way to have two levels from the same dimension on two different axis? If not, what's the best way around this limitation?

    Thank you!

    Simon Blais.

     

All Replies

  • Tuesday, February 19, 2013 9:09 PM
     
     
    Sign In to Vote
    0

    Hi Simon  ,

    You cannot use the same hierarchy on two different axis . We can use one level from hierarchy on one axis and we need to use the other attribute not from the same hierarchy , we need to get this attribute from other user defined hierarchies or from the attribute hierarchies. See the example below 

    select [Date].[Calendar Year].children  on  0 ,
    [Date].[Calendar].[Month]  on 1 
    from  [Adventure Works]
    where 
    ([Measures].[Internet Sales Amount])

    -----------------------------------------------------------------------------------------------------------------------

    Please mark this as answer if this is helpful

     
  • Wednesday, February 20, 2013 7:17 PM
     
     
    Sign In to Vote
    0

    Thank you for taking the time to answer.

    Your proposition works for "non-calculations" measures but when used with a PeriodsToDate calculation, it doesn't add the values from the previous periods. I just get the current value.

     
  • Wednesday, February 20, 2013 8:14 PM
     
     
    Sign In to Vote
    0

    Hi Simon,

    I think you can make use of role playing dimension for this case . Keep the monthName from Date Dimension on row axis and YearName from RolePlaying dimension of same hiearchy on columns to display the PeriodsToDate measure value. But the roleplaying dimension and DateDim should be joined to fact table on the same granular level may be on "Datekey".

    ----------------------------------------------------------------------------------------------------------------------

    Please mark this as answer if this is helpful

     
  • Friday, February 22, 2013 5:50 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Simon,

    Does the following MDX meet your requirement? Please refer to the following Adventure Works sample:
    WITH MEMBER [Date].[Calendar].[Calender2001] AS
        Aggregate(
            PeriodsToDate(
                [Date].[Calendar].[Calendar Year],
               [Date].[Calendar].[Calendar Year].&[2001]
            )
        )
       
     MEMBER [Date].[Calendar].[Calender2002] AS
        Aggregate(
            PeriodsToDate(
                [Date].[Calendar].[Calendar Year],
               [Date].[Calendar].[Calendar Year].&[2002]
            )
        )
          
    MEMBER [Date].[Calendar].[Calender2003] AS
        Aggregate(
            PeriodsToDate(
                [Date].[Calendar].[Calendar Year],
               [Date].[Calendar].[Calendar Year].&[2003]
            )
        )
        

    SELECT
       {[Date].[Calendar].[Calender2001],[Date].[Calendar].[Calender2002],[Date].[Calendar].[Calender2003]} ON Rows,
        [Date].[Calendar Year].Children ON COLUMNS
    FROM
        [Adventure Works]
    WHERE
        [Measures].[Order Quantity]

    If you have any feedback on our support, please click here.

    Regards,

    Elvis Long
    TechNet Community Support

     
  • Tuesday, February 26, 2013 7:18 PM
     
     
    Sign In to Vote
    0

    Hi Simon,

    I think you can make use of role playing dimension for this case . Keep the monthName from Date Dimension on row axis and YearName from RolePlaying dimension of same hiearchy on columns to display the PeriodsToDate measure value. But the roleplaying dimension and DateDim should be joined to fact table on the same granular level may be on "Datekey".

    ----------------------------------------------------------------------------------------------------------------------

    Please mark this as answer if this is helpful

    There's something I don't get because I can't make it work.

    Here's how I defined my calculation in the cube:

    CREATE MEMBER CURRENTCUBE.[Measures].[Transactions amount - YTD]
     AS Aggregate(
           PeriodsToDate(       
            [Time].[Fiscal date].[Fiscal year]
            , [Time].[Fiscal date]
        )   
        , [Measures].[Transactions amount]   
    ),
    VISIBLE = 1;  

    My hierarchy as two levels: Fiscal year and Fiscal month. The key for Fiscal month is Fiscal year and Fiscal month. If I query on a time attribute outside the hierarchy, like Fiscal month (without the relationship to the Fiscal Year), I just have the monthly value, not the cumulative value.

     
  • Tuesday, February 26, 2013 7:28 PM
     
     
    Sign In to Vote
    0

    Hi Simon,

    Does the following MDX meet your requirement? Please refer to the following Adventure Works sample:

    <snip>

    If you have any feedback on our support, please click here.

    Regards,

    Elvis Long
    TechNet Community Support

    Hi Elvis,

    The query you provided gives me yearly totals. What I look for would be more like :

    SELECT
        [Date].[Calendar Quarter of Year].Children ON Rows,
         {[Date].[Calendar].[Calender2001],[Date].[Calendar].[Calender2002],[Date].[Calendar].[Calender2003]} ON COLUMNS
     FROM
         [Adventure Works]
     WHERE
         [Measures].[Order Quantity]

    But still, I don't get the expected results.

    Thanks for your help!

     
  • Tuesday, February 26, 2013 8:55 PM
     
      Has Code
    Sign In to Vote
    0

    Hi Simon,

    Please find the below query  which i used to calculate the  Evolution of month year after year,

    WITH
MEMBER [Measures].[Reseller Sales YTD_2005] AS
Sum( PERIODSTODATE( [Date].[Calendar].[Calendar Year] ),
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2005]) )
MEMBER [Measures].[Reseller Sales 2005] AS
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2005]) 

MEMBER [Measures].[Reseller Sales YTD_2006] AS
Sum( PERIODSTODATE( [Date].[Calendar].[Calendar Year] ),
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2006]) )
MEMBER [Measures].[Reseller Sales 2006] AS
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2006]) 

MEMBER [Measures].[Reseller Sales YTD_2007] AS
Sum( PERIODSTODATE( [Date].[Calendar].[Calendar Year] ),
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2007]) )
MEMBER [Measures].[Reseller Sales 2007] AS
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2007]) 
SELECT
{[Measures].[Reseller Sales 2005],[Measures].[Reseller Sales YTD_2005],
[Measures].[Reseller Sales 2006],[Measures].[Reseller Sales YTD_2006],
[Measures].[Reseller Sales 2007],[Measures].[Reseller Sales YTD_2007]} ON 0,
{[Date].[Calendar].[Month].MEMBERS } ON 1
FROM
[Adventure Works]

    The above query would help you in getting the desired result.

    ----------------------------------------------------------------------------------------------------------------------

    Please mark this as answer if this is helpful

     
  • Wednesday, February 27, 2013 7:25 PM
     
     
    Sign In to Vote
    0

    Hi Nedunuri,

    It's not very far from what I want to achieve, but the results come out as stairs, which makes it hard for year-for-year comparison. If the results for July, August, etc. of each year could be on the same row, that would be perfect.

    The only solution I see at this point would be to add the measure directly in the fact table.

     
  • Wednesday, February 27, 2013 10:58 PM
     
     
    Sign In to Vote
    0

    Can you try this?

    WITH
    MEMBER [Measures].[Reseller Sales YTD] AS
    Sum(
    PeriodsToDate
    (
    [Date].[Calendar].[Calendar Year]
    ),
    (
    [Measures].[Reseller Sales Amount],
    [Date].[Calendar Year].CurrentMember
    )
    ) 

    SELECT
    {
    [Measures].[Reseller Sales Amount],

    [Measures].[Reseller Sales YTD]
    } ON 0,
    {[Date].[Calendar].[Month].MEMBERS } ON 1
    FROM
    [Adventure Works]


    SQL Server BI Consultant (http://EasyBI.wordpress.com)

     
  • Thursday, February 28, 2013 2:23 PM
     
     
    Sign In to Vote
    0

    Hi!

    I wish it was that simple. What I want is a crosstab with that information. I manually edited a picture to show what it could look like (sorry for the French labels).

    Thank you!

     
  • Thursday, February 28, 2013 8:10 PM
     
     
    Sign In to Vote
    0
    Do you have the option to add another attribute hierarchy - Month Of Year - similar to AdventureWorks and try this?

    WITH
    MEMBER [Measures].[Reseller Sales YTD] AS
    Sum(
    PeriodsToDate
    (
    [Date].[Calendar].[Calendar Year]
    ),
    (
    [Measures].[Reseller Sales Amount],
    [Date].[Calendar Year].CurrentMember
    )
    ) 

    SELECT
    [Date].[Calendar Year].[Calendar Year]*
    {
    [Measures].[Reseller Sales Amount],
    [Measures].[Reseller Sales YTD]
    } ON 0,
    {[Date].[Month of Year].[Month of Year].MEMBERS } ON 1
    FROM
    [Adventure Works]

    SQL Server BI Consultant (http://EasyBI.wordpress.com)

     
  • Friday, March 01, 2013 4:09 PM
     
     
    Sign In to Vote
    0

    Unfortunately, this just repeats the value instead of adding the values from the previous months.

    Thanks anyway. :-)

     
  • Friday, March 01, 2013 8:01 PM
     
     Answered
    Sign In to Vote
    1

    Hi Simon  ,

    I hope this would work  as  you expect ,


    with  
    member [Reseller_2005YTD]
    as 
    sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
    [Date].[Calendar Year].&[2005])
    ,([Measures].[Reseller Sales Amount]))
    member [Reseller_2005]
    as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2005]) 

    member [Reseller_2006YTD]
    as 
    sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
    [Date].[Calendar Year].&[2006])
    ,([Measures].[Reseller Sales Amount]))

    member [Reseller_2006]
    as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2006]) 

    member [Reseller_2007YTD]
    as 
    sum((PERIODSTODATE([Date].[Month of Year].levels(0),[Date].[Month of Year].currentmember)*
    [Date].[Calendar Year].&[2007])
    ,([Measures].[Reseller Sales Amount]))

    member [Reseller_2007]
    as ([Measures].[Reseller Sales Amount],[Date].[Calendar Year].&[2007]) 

    select [Date].[Month of Year].[Month of Year].members on 1 ,
    {[Reseller_2005], [Reseller_2005YTD],
    [Reseller_2006], [Reseller_2006YTD],
    [Reseller_2007], [Reseller_2007YTD]} on 0
    from  [Adventure Works]

    The above query would help you in getting the desired result.

    ----------------------------------------------------------------------------------------------------------------------

    Please mark this as answer if this is helpful

    • Marked As Answer by Simon Blais Friday, March 01, 2013 8:27 PM
    •  
     
  • Friday, March 01, 2013 8:27 PM
     
     
    Sign In to Vote
    0

    Wow! That's very promising! I'll be out of the office for the next week but when I come back, I'll have to check how to adapt this to our cube.

    Thanks you very much  and thanks to everyone for your help!

    Simon Blais.

     
  • Friday, March 01, 2013 9:06 PM
     
     
    Sign In to Vote
    0

    Sorry ! Pls try this.


    WITH MEMBER YearName AS 
    RIGHT([Date].[Calendar Year].CurrentMember.Name,4) 
    MEMBER MonthName AS 
    [Date].[Month of Year].CurrentMember.Name
    MEMBER [Measures].[Reseller Sales YTD] AS
    Sum(
    PeriodsToDate
    (
    [Date].[Calendar].[Calendar Year]
    ,
    StrToMember("[Date].[Calendar].[" + MonthName  + " " + YearName +  "]")
    )
    ,
    [Measures].[Reseller Sales Amount]
    )
    SELECT
    [Date].[Calendar Year].[Calendar Year]*
    {
    [Measures].[Reseller Sales Amount],
    [Measures].[Reseller Sales YTD]
    } ON 0,
    {[Date].[Month of Year].[Month of Year].MEMBERS } ON 1
    FROM
    [Adventure Works]

    SQL Server BI Consultant (http://EasyBI.wordpress.com)