locked
YTD RRS feed

  • Question

  • I created a YTD calculation in the cube.

    CREATE MEMBER CURRENTCUBE.[Measures].[YTD-Sales]

    AS Sum (PeriodsToDate ([Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember ),[Measures].[Sales]),

    FORMAT_STRING = "#,#",

    VISIBLE = 1

    When i navigate through hierarchy (Year --> Qtr-->Mth, the YTD is calculated for future month though the data in the cube is till Nov. The Dec months cells are showing the YTD number. The total count is correct but it is confusing to users when they navigate through hierarchy.

    Appreciate if someone can provide guidance.

    Thank you


    Friday, February 6, 2009 8:53 AM

Answers

  • Just one small correction to Mahesh's suggestion,

    IIf(IsEmpty( ([Date].[Calendar].CurrentMember, [Measures].[Sales]) ), Null,  <Your YTD expression> )

    • Marked as answer by IMSHAH Friday, February 6, 2009 5:53 PM
    Friday, February 6, 2009 2:11 PM

All replies

  •  

    Try using CASE statement like

    CASE When ([Date].[Calendar].CurrentMember ),[Measures].[Sales])>0 Then <YTD expression you specified> Else Null END


    ..hegde
    Friday, February 6, 2009 9:03 AM
  • Just one small correction to Mahesh's suggestion,

    IIf(IsEmpty( ([Date].[Calendar].CurrentMember, [Measures].[Sales]) ), Null,  <Your YTD expression> )

    • Marked as answer by IMSHAH Friday, February 6, 2009 5:53 PM
    Friday, February 6, 2009 2:11 PM
  • Thank you Martin for the correction. Right, we should be checking for the existence of the value rather than comparing the value.
    ..hegde
    Friday, February 6, 2009 3:43 PM