This is not my regular Groovy post. Instead, I want to focus on a simple function we use every single day in our planning applications and how we can really push its boundaries, customizing it to fit our specific needs.

Accessing prior or next member data is absolutely essential in almost any application. Think about financial models: whether you’re pulling previous month data for balance sheet calculations or looking ahead for cash flow projections, it’s incredibly crucial. For instance, how do you make sure your ending balance rolls perfectly into the next month’s opening balance?

What do we have in BSO calculations: we have a whole arsenal of functions at our disposal. There’s the @PRIOR function, of course @NEXT function, but also @SHIFT, @SHIFTPLUS, @SHIFTMINUS, and @MDSHIFT. We’ve been using these functions for ages, and they’ve always done the job. But have you ever really paused to think about how they actually work under the hood? My goal here is to dive deep into each of these functions to truly understand their mechanisms.

A quick heads-up before we proceed: The examples and concepts we’ll discuss rely on a lot of underlying conditions, so what works perfectly in one scenario might need tweaking in another. My best advice? Try these techniques out in your own Essbase environment first. Make sure they behave as expected in your specific application, and then, confidently apply them to your real-world calculations.

A Deep Dive into @PRIOR Function

Since many of the functions like @SHIFT, @SHIFTPLUS, @SHIFTMINUS, and @MDSHIFT operate on similar principles as @PRIOR, we’re going to concentrate our efforts on understanding @PRIOR function more.

Prior Function syntax as per the documentation.

To utilize this function, you’ll need to specify the member for which you want to retrieve data. The ‘n’ parameter then allows you to define how many preceding items should be considered in the data retrieval. Additionally, you can leverage the ‘xRangeList’ to access a customized list.

If you don’t specify a custom list, the @PRIOR function will default to fetching data for the previous month. In this scenario, it automatically generates a custom list using the level zero members of the Period dimension, which is typically tagged as the Time dimension

A simple @PRIOR Function

Before Execution:

After Execution:

Everything looks good so far; my previous month’s closing data from Aug to Jun is getting assigned to the current month’s opening data. However, I want my July data to be calculated as well.

why July month is #Missing?

To understand why July data isn’t being calculated, we need to revisit the Period Dimension hierarchies. As mentioned in the documentation, the @PRIOR function fetches prior month data based on the Period Dimension’s level zero members. Here is my Period Dimension hierarchy.

Let me add some data to my BegBalance and then run the rule.

That’s great! It looks like it’s working exactly as the documentation describes, with July fetching data from BegBalance, which is what we anticipated. However, in a real-world scenario, we won’t always need to pull July’s data from BegBalance. There will be times when we’ll actually need to fetch it from the previous year’s last period instead like Closing Balance data.

Lets explore the Custom Ranges in @PRIOR function

I’d really prefer not to rely on the default Level 0 members that the @PRIOR function typically pulls. What I need to do is specify my own ranges for where @PRIOR should fetch its data. So, let’s dive into how we can apply those ranges within the @PRIOR function.

When I’m specifying these custom ranges, I also need to provide a number for prior member indexing. A positive 1 here simply means to go back to the immediate previous member in that range.

Before Execution:

After Execution:

Here’s the interesting part: the bad news is that July is showing ‘#Missing’ data. But, that’s also the good news, because at least it’s not pulling data from BegBalance. Think about it: if you had some assumption data sitting in BegBalance and the system copied it over as valid, that would be far worse than having ‘#Missing’ values. Incorrect data is definitely something we want to avoid!

So, what’s happening is that because we’ve provided custom ranges of “Jul” to “Jun”, the system understands there’s no prior period for “Jul” within that specified range, and it populates it with ‘#Missing’.

How to Populate July with Prior Year and June Data?

ADD YEARS to the RANGE using @XRANGE

In our previous attempt, we simply passed a range of members. But what if we convert that into an XRANGE LIST? We can transform that prior calculation into an XRANGE list like this:

Well, this also gives us the same outcome: July is still showing as missing. Thats because we have not mentioned FY24-> Jun as the prior member here.

Adding FY24->Jun before FY25->Jul

Alright, we’re finally going to see the results for all 12 periods now. And, good news, the data is flowing from the previous year to the current year exactly as we’d hoped!

But How Does the @PRIOR Function Correctly Get the Previous Year and Previous Month using @XRANGE?

he answer is actually pretty simple! It’s not like the @PRIOR function magically pulls the previous member from the Year and Period dimensions in the hierarchy. What it really does is look for ‘FY25′->’Jul’ within the XRANGE list you provide and then just grabs the member that comes right before it in that list.

So, let’s consider this: what do you think would happen if you put ‘FY26′->’Jun’ before ‘FY25′->’Jul’ in your XRANGE list?

Before Execution:

After Execution:

So.., no one in the financial world would ever copy a closing balance from the next year into the current year. So, to ensure this whole process works correctly, we absolutely have to make sure that the XRANGE members we provide are in the right format and presented in the correct order.

THE @XRANGE MULTI-YEAR PROBLEM

We’ve achieved our primary goal: the current year’s July data is now correctly referencing the prior year’s June, and all other current year data is accurately pulling from the prior months. So, one might think we’ve completely solved the issue.

However, what if your current forecasting process isn’t limited to just one year, but extends beyond that? Will our current setup still work as intended? Let’s investigate to find out

The outcome:

The problem we’re facing now is that FY26->Jul isn’t pulling any data, which suggests our XRANGE list is indeed messed up. We’re back to examining the hierarchy to understand what’s going wrong. It seems we’ve run into the same ‘BegBalance’ issue here again.

When we use @XRANGE(“FY25″->”Jul”, “FY26″->”Jun”), it brings in the following list of members:

I believe XRANGE is generating a list from level zero members. If we span multiple years, FY26->BegBalance also gets added to the list and is just considered a valid intersection.

How Do We Remove “FY26″->”BegBalance” from the @XRANGE List?

We can use the @REMOVE function to get rid of any unwanted items from a list. Let’s look at how we can remove a specific range member.

Now that our XRANGE list is free of BegBalance, let’s see if this finally gets us the results we’re looking for:

Before Execution:

After Execution:

YEAR RANGE in the Exclusion(@Remove) List:

In a typical application, we’d be dealing with a lot more factors. For instance:

1. We won’t just have two years; we’ll have multiple years to consider.
2. There will be more assumption members, including BegBalance, making XRANGE exclusions a bit more complicated.

So, how do we handle these exclusions effectively? Let’s try adding the year range to our exclusion list.

Adding Year Ranges in the Exclusion List

Hardcoding only specific members will definitely have an impact down the line. If someone adds a period member in the future, it could completely mess up our calculations.

So, what if I simply keep only the periods from July to June? I’ll expand the remove list to exclude all members except for those July through June months.

Is it working? Not really. The reason is, we also need to exclude the shared members from our exclusion list.

Lets check the results:

Awesome! It successfully removes all members except July to June from the XRANGE list, and gives me exactly the results I was hoping for.

Is that all necessary? Well, it depends.

You could write that @PRIOR function with an exclusion list of XRANGE member ranges or you could write something like the below one.

IF check with @PRIOR Function

Alright, now let’s talk about using the @ISMBR function with @PRIOR. This is particularly useful because @PRIOR can work across other dimensions, like ‘Year’, not just ‘Period’. So, how do we leverage @ISMBR to ensure we’re targeting the right members when fetching prior values, especially when we’re dealing with multiple dimensions?

If you really Like @MDSHIFT function

And…

It’s certainly beneficial to have a variety of options when you’re writing a business rule under tight deadlines. I consciously decided not to delve into the nuances of performance, block/cell mode calculations, serial/parallel processing, or bottom-up/top-down scripting in this post. This article is already quite extensive, and I wanted to keep it focused.

My main point is this: instead of simply following existing examples, including those on my own blog, I encourage you to explore what genuinely works best for your specific needs. I sincerely hope this information proves useful.