Supercharge your RTP Validations with Groovy — Date using Global Assumption

Validate Dates Like a Pro: Supercharge Your RTP Validations with Groovy

Here’s another variation on validating dates to check that it falls within the start and end of the plan duration/window, another way to check dates is using a global assumptions form.

Validating the date

First we need to capture the start date and end date in an assumptions form. So let’s start with the metadata.

Metadata for Global Assumptions

Then let’s create a simple form for users to input the dates.

Simple form to enter Global Assumptions

I have designed this to take start and end date for Plan as well as Forecast. You can of course extend this to cover any other scenarios.

Date selector to enter dates based on a calendar

Dates we’re using for the validation example

Let’s get into the script now

/*RTPS: {Scenario}, {validateDate}*/
Date startDate, endDate

Cube cube = operation.application.getCube("OEP_FS")
FlexibleDataGridDefinitionBuilder builder = cube.flexibleDataGridDefinitionBuilder()

builder.setPov('OEP_No Version', 'No Currency', 'No Entity', 'No Product', 'OFS_Direct Input', 'No Market', 'No Year', 'BegBalance')
builder.addColumn(rtps.Scenario.EnteredValue)
builder.addRow('ILvl0Descendants(Accounts - Global Assumptions)')

// Load a data grid from the specified grid definition and cube
cube.loadGrid(builder.build(), false).withCloseable { grid ->
 grid.dataCellIterator().each { cell ->
     if (cell.AccountName == "Plan Start") {
         startDate = cell.DataAsDate
        } else if (cell.AccountName == "Plan End") {
         endDate = cell.DataAsDate
        }
    }
}

def mbUs = messageBundle (
 ["DATE.DateEarlier":"The entered date \"{0}\" is earlier than the start date \"{1}\". Please enter a different date and try again.",
     "DATE.DateAfter":"The entered date \"{0}\" is after the end date \"{1}\". Please enter a different date and try again."
 ] )
def mbl = messageBundleLoader(["en" : mbUs])

validateRtp(rtps.validateDate, {!(rtps.validateDate.Date < startDate)}, mbl, "DATE.DateEarlier", rtps.validateDate.Date.format("dd/MMM/yyyy"), startDate.format("dd/MMM/yyyy"))
validateRtp(rtps.validateDate, {!(rtps.validateDate.Date > endDate)}, mbl, "DATE.DateAfter", rtps.validateDate.Date.format("dd/MMM/yyyy"), endDate.format("dd/MMM/yyyy"))

We’re accessing the DataGrid to fetch the data from the same intersection as the form that we just saw. If you’re not familiar with the DataGrid, that’s going to be in a different post.

So having read the data from the Plan Start and Plan End cells, we now have the dates in the variables. The validation is just like it was earlier.

Entering a date after 31/Dec/2023

The “later than” validation kicks in

Entering a date that is within our Plan Start and Plan End dates

And no validation error!

Wrap up

We’ve used a global assumptions form to define the start and end of the planning horizon and validated the entered date against it.