Skip to main content

Relational Planning Functions

Expressions for calculated measures can include any SQL scalar function. In addition, some specific RP functions are defined, as detailed below.

ApplyMeritIncrease(base_amount, increase_rate [, divide_periods])​

Applies a merit increase to a base amount, starting on the anniversary date. If increase_rate is between 0 and 1, it is considered a percentage increase, otherwise it is considered a amount increase. The merit increase gets applied cummulatively for each year. Note that the increase is first applied once a full year has passed after the anniversary date. If divide_periods is not provided, it defaults to 1, meaning the increase is applied to the full base amount. otherwise the result is divided by divide_periods.

ApplyTimedIncrease(base_amount, increase_rate, increase_date, [, divide_periods])​

Applies an increase to a base amount, starting on increase_date. If increase_rate is between 0 and 1, it is considered a percentage increase, otherwise it is considered a amount increase. The increase gets applied cummulatively for each year. Unlike ApplyMeritIncrease(), the increase rate gets applied right away starting on increase_date If divide_periods is not provided, it defaults to 1, meaning the increase is applied to the full base amount. otherwise the result is divided by divide_periods.

IsAnniversaryMonth()​

Returns 1 if the current period is in the same calendar month as the anniversary date Note: Will not work with non-calendar periods (e.g. 4-4-5)

IsActive()​

Returns 1 if the current item is active during the current period (even partially) or 0 otherwise. Must be used like iif(IsActive() = 1, active_value, inactive_value)

Prorate(value)​

Prorates a monthly value based on the overlap between the period start/end dates and the start/end dates of the item

ProrateDaysInYear()​

Returns the number of active days in the period divided by total number of days in the year, based on the overlap between the period start/end dates and the start/end dates of the item

ProrateDaysInCalendarYear()​

Returns the number of active days in the period divided by total number of days in the calendar year, based on the overlap between the period start/end dates and the start/end dates of the item

YearlyAccumulatedValue(measure)​

Calculate yearly accumulated value for a calculated measure. Sums up the values of measure for all prior periods in the same year. Note that measure must be a measure, it cannot be an expression.

CalendarYearAccumulatedValue(measure)​

Calculate calendar year accumulated value for a calculated measure. Sums up the values of measure for all prior periods in the same calendar year. Note that measure must be a measure, it cannot be an expression. Note: this function assumes that periods do not span across calendar years

ApplyCappedRate(measure, cap, rate)​

Calculates measure * rate as long as the accumulated annual value for measure is <= cap Uses a fiscal year cap (as defined in the time dimension). Note that measure must be a measure, it cannot be an expression. It's basically the same thing as writing:

iif(YearlyAccumulatedValue([measure]) >= [cap], 0, [rate] * least([measure], [cap] - YearlyAccumulatedValue([measure])))

ApplyCYCappedRate(measure, cap, rate)​

Like ApplyCappedRate() but uses a calendar year cap. It's basically the same thing as writing:

iif(CalendarYearAccumulatedValue([measure]) >= [cap], 0, [rate] * least([measure], [cap] - CalendarYearAccumulatedValue([measure])))