Skip to main content

NOW Member (MDX)

This section provides information on the NOW member for date/time levels. This syntax is an InterSystems extension to MDX.

Basic Syntax

date_time_level.[NOW]

Where:

This syntax returns the member of the given level that corresponds to the current date and time. The following table shows examples (created on 24 May 2012):

Expression Returned member
birthd.decade.NOW birthd.decade.2010s
birthd.[quarter year].NOW birthd.[quarter year].[Q2 2012]
birthqd.[quarter].NOW birthd.[quarter year].Q2
birthtd.NOW birthtd.4pm

NOW is not case-sensitive. Also, the square brackets around it are optional unless you are using one of the variations discussed below in this topic.

Dates Relative to Now

You can also use expressions of the following form:

date_time_level.[NOW-integer]

Or:

date_time_level.[NOW+integer]

The following table shows examples (created on 24 May 2012):

Expression Returned member
birthd.decade.[NOW-4] birthd.decade.1970s
birthd.[quarter year].[NOW-4] birthd.[quarter year].[Q2 2011]
birthqd.quarter.[NOW-4] birthqd.quarter.Q2

Be careful when using such expressions with a time level that is independent of the overall calendar. In such cases, [NOW-integer] refers to an earlier bucket in a cycle. Notice the third example in the table. The birthqd.quarter level groups records by quarter, independent of year. For this level, NOW refers only to the current quarter number. This level has four members in a cycle, and for this level, [NOW-4] is equivalent to [NOW].

The same logic applies to expressions of the form [NOW+integer].

Restrictions on Use in Range Expressions

You cannot use NOW in range expressions for a level that is based on any of the following time functions:

Time Function... Typical Members Notes
QuarterNumber Q4 These levels are independent of the year
MonthNumber November
DayNumber 24 This level is independent of the year and the part of the year
HourNumber 1am These levels are independent of the day
MinuteNumber 01:24

Additional Options for Day Levels

For a level that is based on the DayMonthYear time function, Business Intelligence supports the additional expressions that use a combination of year count, month count, and day count, as follows:

daymonthyear_level.[NOW-offset_expression]

Or:

daymonthyear_level.[NOW+offset_expression]

In simple cases, offset_expression is nnynnmnnd, as follows:

  • nn is a one or two digit integer.

  • The optional unit nny specifies the number of years.

  • The optional unit nnm specifies the number of months.

  • The optional unit nnd specifies the number of days.

The following table shows examples (created on 24 May 2012):

Expression Returned member
birthd.date.[NOW-4y3m2d] birthd.date.[Feb 22 2008]
birthd.date.[NOW-1m] birthd.date.[Apr 24 2012]

By default, the units are added together. For example, 4y3m2d means four years plus three months plus two days.

You can instead include a minus sign between units to subtract. 1y-1d means one year, minus one day. For example:

Expression Returned member
dateofsale.actual.daysold.[NOW-1y-1d] dateofsale.actual.daysold.[May 25 2011]

The system automatically accounts for leap years. The internal logic also accounts for the varying lengths of the months.


FeedbackOpens in a new tab