5g: ScheduleRatesByItemsλ()
Description
ScheduleRatesByItemsλ() places values for selected items from a schedule table (or array) in a timeline's periods. The rate continues for each period after until another rate begins. This can be used for rental rates, pricing, discounts, etc.
Syntax
ScheduleRatesByItemsλ(PeriodEnds, Items, ItemRates, RateStarts, [Diagnostics])
The ScheduleRatesλ function has the following arguments:
PeriodEnds (Required)
A row or column of period end dates (timeline).
ItemFilter (Required)
1 or more items to filter a rate schedule by.
ItemList (Required)
Items in a rate schedule. A 'rate schedule' is a table/list where each row contains a unique item/effective date pair with a rate.
EffectiveList (Required)
Effective dates in the rate schedule.
RateList (Required)
Rates in a rate schedule for each item/effective date pair.
Diagnostics (Optional)
If TRUE, input errors that can occur after formula entry will display as diagnostic messages. The default is FALSE. It is recommended that models have a named range called Diagnostics and this named range be set to TRUE during model development and then set to FALSE when the model is placed into production.
This function includes an argument named DoNotUse. This is needed for internal purposes and must be omitted when the function is first called. So, do not use DoNotUse.
Example
In this example we are scheduling price changes. On the left we have a table of items, price change effective dates, and prices. Over our component is a timeline of period end dates. The order of entries in the table is unimportant. The rates and effective dates can be in a horizontal layout. No table is needed but we lose the dynamic advantages of structured references.
For our model to be accurate, our timeline's period interval must be as frequent or more frequent than the rate at which rates can change. In other words, if 2 or more rates are schedule for the same period in the timeline, only the last will be scheduled.