none
YTD MDX query RRS feed

  • Question

  • Hi All,
    I am unable to get the right MDX for YTD in the data mapping.
    My requirement is like this-

    I have a Time Intelligence filter with 2 parameters- Current Year and Current Month.
    My scorecard is like this-
              Actual      YTD      Last Yr      Last Yr YTD
    kpi1
    kpi2
    .
    .
    .

    When I filter on Current Year, I should get the Actuals and YTD as the same value considering that the Year starts from March (Reporting Year). I am getting this with the query below:

    SUM({ [Time].[Reporting Year -  Reporting Quarter -  Reporting Month -  Reporting Week -  Reporting Day] :[Time].[Reporting Year -  Reporting Quarter -  Reporting Month -  Reporting Week -  Reporting Day].CurrentMember },[Measures].[Invoiced Cases])

    But this does not work for Current Month. I get the YTD for the current month same as the Actual for the current month, which should not be the case. (The query above is just calculating the actuals if you notice)

    Can anyone tell me the correct range that needs to be given in the mdx query to get YTD?

    Thanks,
    Roopali
    Friday, February 13, 2009 11:44 PM

Answers

All replies

  • You will want to look at implementing the PeriodsToDate function.  Your MDX for this calculation should look like the following:

    SUM(PeriodsToDate([Time].[Reporting Year - Reporting Quarter - Reporting Month - Reporting Week - Reporting Day].[Reporting Year],[Time].[Reporting Year - Reporting Quarter - Reporting Month - Reporting Week - Reporting Day].CurrentMember ),[Measures].[Invoiced Cases])

    The PeriodsToDate function accepts a level and member expression, so if the level in your hierarchy is not 'Reporting Year' then you will need to modify that.

    If you need to get values for previous periods then you will want to explore the ParallelPeriod function too.


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Proposed as answer by Dan English Saturday, February 14, 2009 7:31 PM
    Saturday, February 14, 2009 12:24 PM
  •  Hi Dan,
    I have tried YTD and PeriodsToDate...it throws up an error. I had used the same formula as you have mentioned.

    The post below indicates that these are not working correctly in dashboard designer:
    http://social.technet.microsoft.com/Forums/en-US/ppsmonitoringandanalytics/thread/4cf67a60-b239-4f44-b7a1-6dadb7b66424/

    For the Last Yr value i have used ParallelPeriod itself and for Last YTD, i am using ParallelPeriod: currentmember range. I think this should not be a problem.

    Only problem I am facing is with YTD.

    Using time intelligence YTD, i do get accurate values but the problem is with the display there.
    It shows up each month as a column present in the range rather than aggregating them into 1 column for YTD.
    If this issue can be solved, then I will go ahead with the YTD function of time intelligence.

    I just need to get the range correct. All i want is when I select Current Month as my filter value, the YTD value should not change. Will a case statement on the filter value work for this? Kindly let me know.

    Thanks,
    Roopali

    Saturday, February 14, 2009 2:33 PM
  • The formula does work and the posting you reference also does work.  It is most likely because of your Time Intelligence Formula.  I am assuming you are trying to incorporate Current Year into the filter as a YTD forumula like Year.FirstMonth:Month.  That is not going to work because of the CurrentMember reference in the MDX Tuple Formula.  That would return a set instead of a single member and will return an ERROR.  You would need to use formulas like the following:

    Filter Name        Forumula

    Current Month    Month
    Current Year        Year


    The MDX Tuple forumula will return your aggregated value that you are looking for once you create the link to the scorecard and set the association up properly as a filter and your data source.


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Saturday, February 14, 2009 4:46 PM fixing typos of formula
    • Proposed as answer by Dan English Saturday, February 14, 2009 7:32 PM
    Saturday, February 14, 2009 4:44 PM
  • Yes that post does show it should work. Sorry about that. Dont know whats the issue then.

    Regarding time intelligence-
    I have used the same as you stated above. Well i did not mean to confuse you.

    I have used this-

    Formula                                             Name
    Month                                                Current month
    Year                                                   Current Year
    Year.FirstMonth:Month                      YTD

    I said that if i use the YTD formula- Year.FirstMonth:Month in my time intelligence, i do get accurate results.
    But the problem with this is, that it does not show aggregated results i.e it shows me the measure val for march, april, may....all as seperate columns. It does not display YTD as a single column "YTD", if YTD of time intelligence is considered.
                    march             april              may            ....

    kpi1            val                val                 val        

    kpi2             val                val                 val
    .
    .    
    .
    .



    Hope its clear this time, what i mean.

    Since this would expand the width of my scorecard with all months coming up in columns, I have removed the YTD calculation from time intelligence filter and now I am adding a new column YTD of each kpi. Therefore need the mdx for data mapping.

    Let me know if you got it this time.

    Thanks,
    Roopali.
    Saturday, February 14, 2009 5:35 PM
  •  http://social.technet.microsoft.com/Forums/en-US/ppsmonitoringandanalytics/thread/88dc9f93-9202-42c9-a14f-8dee56325473/

    This post too indicates that the feature of aggregating and getting a single column of YTD is not present.
    Saturday, February 14, 2009 5:52 PM
  • You really need to provide information as to how you have the scorecard setup.  What is on rows, columns, etc.?  By looking at your most recent example it appears that you have the Date hierarchy on the Columns.  Using the Time Intelligence filter is simply going to filter the members returned on the columns, this does not provide aggregated values.  You need to design your scorecard appropriately based on what you are trying to actually display.

    The MDX I provided is what you would use in the MDX Tuple Calculation for a YTD value on the KPI.  Add this into your KPI along with your other values.  Remove the YTD selection from your Time Intelligence filter since it is not needed and remove the Date hierarchy from the columns.  Incorporate the logic into the design of the KPIs.

    Other ways that you can get at these values would be looking at implementing what is known as a Shell dimension in your cube.  SSAS has built-in wizards used to generate aggregated time values like MTD, QTD, YTD, etc. that you could also use, but not quite as flexible since they have to keep getting modified to incorporate new measures.  Marco Russo has a nice tool that you can use called DateTool - DateTool dimension: an alternative Time Intelligence implementation.  Once this is setup you can simply select the members from the new dimensions.

    Getting at the values and layout you want is definitely doable, you just need to layout the items properly.  If you want additional assistance then please provide more detail as how you currently have your KPIs and Scorecard items designed and setup, otherwise I will have to continue to keep guessing at a solution.

    The last link you provided is definitely based on how you currently have your scorecard probably setup with the Filter tied to the Columns and the Date hierarchy in the columns, which simply filters the members and does not generate the aggregated results that you are looking for.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Saturday, February 14, 2009 6:07 PM
  • Well i have read about the shell dimension and kept it as my last otpion. May be i can just do this in the cube level itself...atleast the PeriodToDate would work there.

    There is just the Reporting year hierarchy and data is at month level. I am getting the following-

    Filter- Current Year

                                    Actual                    YTD                            Prev Yr

    Invoiced Cases        106186880           106186880                132315065


    Filter - Current Month

                                       Actual                    YTD                           Prev Yr

    Invoiced Cases         6695025               6695025                   16,477,331

    I want that only the YTD to remain same as 106186880 when filtered on Current Month. I just need the right mdx maybe with a case stmt based on the value of the filter.

    Is it clear this time?

    Thanks,
    Roopali.
    Saturday, February 14, 2009 6:27 PM
  • Using the formula that I supplied in the KPI for the YTD output will provide the results that you are looking for.  You will then need to incorporate similar MDX into prior period values that you are looking for.  You will then setup the Time Intelligence to filter the scorecard (not rows or columns, it will be a background filter).  So your KPIs will have Actual, YTD, Prev Yr, Prev YTD, etc. setup.

    If you don't want to incorporate the logic as an MDX tuple forumla in the YTD setup for the KPI you could select the measure and then add the Time Intelligence filter formula of 'Year'.  This would also give you the same result, just wouldn't be quite as dynamic then with the filter setup on the dashboard.

    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Saturday, February 14, 2009 7:05 PM
  • Are you talking about this mdx-

    SUM(PeriodsToDate([Time].[Reporting Year - Reporting Quarter - Reporting Month - Reporting Week - Reporting Day].[Reporting Year],[Time].[Reporting Year - Reporting Quarter - Reporting Month - Reporting Week - Reporting Day].CurrentMember ),[Measures].[Invoiced Cases])

    I think I did mention it that this throws up an error. PeriodsToDate is not working here for some reason.

    I have taken my mdx as 

    SUM({ [Time].[Reporting Year -  Reporting Quarter -  Reporting Month -  Reporting Week -  Reporting Day] :[Time].[Reporting Year -  Reporting Quarter -  Reporting Month -  Reporting Week -  Reporting Day].CurrentMember },[Measures].[Invoiced Cases])

    and it shows me the correct value for Current Year filter.

    But for the month filter, it shows me the same month value for actuals and for YTD as shown in my prev post(that is cos the mdx i gave is selecting nothing but the current range).

    Saturday, February 14, 2009 7:18 PM
  • I will try out that built in time intelligence wizard now. Dont have any good option and not much time either.

    Thanks for your help anyways.

    If you get any solution do let me know.

    Thanks,
    Roopali.
    Saturday, February 14, 2009 7:28 PM
  •  If the Level member is not 'Reporting Year' then you will need to change that, but yes, that is the MDX Tuple Formula you will want to use when you setup the KPI for Invoiced Cases YTD (in the Actual and Targets section of the KPI in the data mappings).  The MDX range that you are currently using is simply returning the value based on your filter, so that is why you are not getting YTD value when you filter on Current Month.

    If you were getting an ERROR! before when using that it would have been when you were selecting the YTD filter that was setup Year.FirstMonth:Month and I have already explained why that was occuring with the CurrentMember (needs to reference a member, not a set).  The MDX I provided will work with the Current Month and Current Year filter that you have setup.  If it is not working for you then you will need to review how you have your scorecard setup and how you have created the links from the filter to the scorecard.

     

     

     


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Saturday, February 14, 2009 7:29 PM
  • I have uploaded a sample bswx file to my SkyDrive account for you to download and review if you would like.  This is based off the Adventure Works DW SSAS database.  You will need to modify the PPS connection and data source information, but you can at least see how I defined the KPI, scorecard, and filter.  I have this going back five years to get at information in the Adventure Works.  Now the YTD and Current Year combination will actually pull the total for all of 2004 since there is data loaded for that year, but you can get the general idea.  The MDX and filters do work as you want them to.

    Adventure Works DW Sample - Time Intelligence Filter and MDX
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Saturday, February 14, 2009 7:44 PM
  • Rupali,
    To do these calculations sometimes become hard because of our cube structure,what massages you are getting when you are applying the formula,it should give some kind of massage when you brows that and point your cursor there it will show you error massage
    These kinds of calculations first try in the management studio as a calculated measure

    with member measures.ytd_a as
    ---your formula
    select  measures.ytd_a on 0 from xyz

    in management studio if it works or not but you can see the errors by pointing your cursor to your result.
    Let us know what error you get

    Dibyant S U padhyay
    Saturday, February 14, 2009 8:09 PM
  • Rupali,
    Also try this link which is helpful
    http://blogs.conchango.com/christianwade/archive/2006/06/23/MDX-Script_3A00_-Current_2F00_Relative-Period.aspx

    another one very very helpful

    http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

    Thanks and let me know if you have any further questions
    Note:  Time shell dimension doesn't have any performance  or any other issues(But building YTD using Time business Intelligence Wizard have few limitations)
    Thanks

    Dibyant S U padhyay
    Saturday, February 14, 2009 8:31 PM
  • Hi,

    Wasting no more time on this, I finally added the Business Intelligence in the cube and used it in my scorecard.
    Its working fine now :-)

    By having the calculation in the cube i can get the YTD even in ProClarity or any other reporting tool.

    Guess there was no way out other than doing it in the cube level itself because when you filter by current month, the ytd value used to change (since the mdx was not right).

    But if the YTD is coming from the cube, then the filter on Current Month shows the actual YTD itself and not modify it.

    Anyways thanks to both of you, Dan and Dibyant for replying to my queries and trying your best to help me out.

    Thanks,
    Roopali. :-)
    Saturday, February 14, 2009 8:33 PM
  • Yes I followed the "Adding Time Intelligence Using the Built-In Wizard " given in the link:

    http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

    I know about the limitations and had kept this as my last option since past few days.
    Now due to time constraint, I chose this as it was the easiest and working option.
    Also, this suits my requirement and the limitations dont hold good for the time being.

    Thanks,
    Roopali.
    • Marked as answer by Roopali Dewan Saturday, February 14, 2009 9:03 PM
    Saturday, February 14, 2009 8:52 PM