locked
Trouble calculating YTD RRS feed

  • Question

  • I'm trying to create a YearToDate calculated measure that gives me YTD sales.  However, when I add the following calculated member:
     
    Sum(YTD([Measures].[Sales]))

    with "Measures" as the parent, I get the following error when I try to use the calculated measure:

    "The YTD function exects a member expression for the argument.  A string or numeric expression was used"

    I'm using SQL 2005.  Any suggestions?

    Trent
    Monday, November 14, 2005 10:00 PM

Answers

  • Try going throught the Tutorial.
    Lesson 6 talks about how to define calculations.


    Edward Melomed (MSFT)


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, November 16, 2005 6:53 AM

All replies

  • Here is article you can take a look at
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbpj00/html/complex.asp

    Edward Melomed
    -------

    This posting is provided "AS IS" with no warranties, and confers no rights.

     

    Tuesday, November 15, 2005 3:39 AM
  • Thanks for the link - it helped correct some of the syntax.  However, this doesn't really solve my problem.  Sure you can get YTD calculations in an MDX query, but this doesn't necessarily translate into the correct expression to use when creating a calculated measure.  When I put the following:

    SUM(YTD(),[Measures].[Net Sales])

    In the Expression box of the calculated field dialog, it returns nothing in the field when I process the cube and try to display data.  Full MDX query syntax isn't supported in the calculated measure expression dialog (WITH and SELECT give me errors).

    I'd like to know what syntax goes in the calculated measure expression dialog to get a YTD calculation for the current year.

    Wednesday, November 16, 2005 5:51 AM
  • Try going throught the Tutorial.
    Lesson 6 talks about how to define calculations.


    Edward Melomed (MSFT)


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, November 16, 2005 6:53 AM
  • I appreciate the response, but again, this is great for doing the calculation in an MDX query, but creating a stored calculated measure in the cube is a different matter and requires a different syntax.  I've created other calculated measures that work, but this YTD function seems to operate in a different way because it never returns values even though it's an extremely simple calculation.
    Wednesday, November 16, 2005 8:14 AM
  • How do you define your calculated measures?
    Are you using measure expression property of the measure or you are building calculations using Calculation editor as described in lesson 6 ?


    Edward Melomed (MSFT)
    ----
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, November 17, 2005 6:41 AM
  • The mesure is created in the "calculations" tab of the Cube designer in Visal Studio 2005.  The expression is entered in the "expression" dialog on that page and the parent is set to "measures".  The expression i'm entering is:

    Sum(YTD(),[measures].[sales])

    The alternative syntax:  Sum(YTD([Measures].[Sales])) also works the same.

    I've created many other calculated measures in this way and they work fine - it's the YTD function that it seems to have problem with.

    Microsoft supplies the BI functions that can be added to your projects, but they are added as a single calculated dimension with many atrributes and they use the Aggregate and PeriodsToDate functions to accomplish the YTD calculations (they don't even use the YTD function themselves).  Unfortunately, this won't work for my application (i need it as a calculated measure).  I've tried using PeriodsToDate and I get the same results - nothing.
    Thursday, November 17, 2005 4:37 PM
  • Can anyone help me in writing a fuctional query in SQL SERVER 2005 to calculate the measues in YTD MTD and QTD

     

    Monday, August 20, 2007 5:45 AM
  •  

    this is not working its giving me error with the above statement, is there anyother solution for this
    Monday, August 20, 2007 6:40 AM
  • i am using the calculation part as described in lession 6

     

    Monday, August 20, 2007 6:43 AM
  • thanks for ur suggestion boss but in that its not speicifed as to how should we calculate. i want to calcute the measures using function and i want the output in MTD , QTD,YTD

     

    Monday, August 20, 2007 6:46 AM
  • i am getting error with ur commnd that have been provided , can u just help me

     

    Monday, August 20, 2007 9:17 AM