Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: YTD() and QTD() how to make them work ?

  1. #1

    Default YTD() and QTD() how to make them work ?

    Hi everybody,

    I am facing a problem trying to use YTD() and QTD().

    To begin with there is my Time Dimension :

    Code:
    <Dimension name="Time" type="TimeDimension">
            <Hierarchy name="TimeByQuarter" hasAll="true" allMemberName="All Periods" primaryKey="_id">
            <Table name="dimtime"/>
                        <Level name="Year" column="year" type="Numeric" uniqueMembers="false" levelType="TimeYears"/>
                        <Level name="Quarters" column="quartal" uniqueMembers="false" levelType="TimeQuarters" />
                        <Level name="Month" column="month" uniqueMembers="false" ordinalColumn="month" nameColumn="monthname" levelType="TimeMonths" type="Numeric"/>
                        <Level name="Day" column="day" uniqueMembers="false" ordinalColumn="day" nameColumn="weekdayname" levelType="TimeDays" type="Numeric"/>
            </Hierarchy>
            <Hierarchy name="TimeByMonth" hasAll="true" allMemberName="All Periods" primaryKey="_id">
            <Table name="dimtime"/>
                        <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears"/>
                        <Level name="Month" column="month" uniqueMembers="false" ordinalColumn="month" nameColumn="monthname" levelType="TimeMonths" type="Numeric"/>
                        <Level name="Day" column="day" uniqueMembers="false" ordinalColumn="day" nameColumn="day" levelType="TimeDays" type="Numeric"/>
    	</Hierarchy>
    </Dimension>
    And this is what's I am trying to do :

    Code:
    with member [Measures].[Test] as SUM(YTD(), IIf(([Measures].[ProgressLevel] = 1.0), 1.0, 0.0))
    The problem is that I always get a 0 value as [Measures].[Test] and I know that in one specifique month it goes to 1 so I don't really understand what's happening.

    I tried specifying within the Ytd() the Time dimension I wanted but no luck (eg. Ytd([Time.TimeByMonth].[Year].Members)

    Does anyone know what am I missing ? If you need me to give you the query don't hesitate to ask.

    Thank you in advance,

    --
    Spredzy

  2. #2
    Join Date
    Apr 2009
    Posts
    337

    Default

    syntax for ytd as posted here:

    http://msdn.microsoft.com/en-us/library/ms146039.aspx

    guess im reposting this for the second time... let me know if this helps you to write a proper query
    Regards,
    Madhu

  3. #3

    Default

    On the expression I posted the YTD() syntax is perfectly correct.

    When no parameter specified, it will look from [Time].CurrentMember level first sibling to [Time].CurrentMember.

    Problem was from the construction of my Time dimension inside the XML Mondrian schema. Problem is solved now but there is no issue with the YTD() syntax.

    Thanks for answering and helping me out,

    I appreciate,

    --
    Spredzy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.