Value from wrong version is returned

Hi,
I have a problem where I get value summed by a wrong version returned.
From a module without any versions, I want to pick up FTE values from budget version in another module which has versions and sum by which role the employee has.
The formula looks like this:

'FTE Module'.'FTE'[SUM: 'FTE Module'.'HR-Role', SELECT: VERSIONS.Budget]

The formula above returns the correct FTE value but sums by the HR-role that was entered in Forecast version (which is set as current version if that could be important information).

Any suggestions why this happens?

Description of attached filescreen dumps:

pic1: Blueprint of the module in which I want to present budget FTE data summed by HR-Role.

pic2: Blueprint of the module from which FTE data is collected.
pic3: Presents the data of my test person, which is the data I want to collect. As you can see he's set to HR-Role "Admin local" in Budget version (and "Consultants local" in Forecast version). FTE value is set to 1 for budget version and 0 for forecast version. (Blueprint of module in pic2)
pic4: Result of how data was collected (module blueprint in pic1). As you can see, the FTE value is correct but person (which is the only person under list item "610 - sweden") gets HR-Role for forecast version instead of budget version.
Clarification: list O5-department roster and list O4-PC in described modules are hierarchical lists.

Anaplan_question_85521.jpg

Accepted answers

AlejandroGomez

I have reviewed the modules that I created to replicate your case and found the issue:

The clauses SUM and SELECT in the formula in the "Target" module only applies to the "FTE" line item in the source module:

formula behaviour.JPG

That means that the formula indeed retrieves the correct number of FTEs from the correct version. But since the "SELECT" clause does not apply to the Line ITem "Role", the formula retrieves the Role entered in the Forecast version (which is the current version and thus the one that Anaplan uses by default).

As a result, Anaplan is summing the FTEs from the "Budget" version (the version specified with the SELECT clause in your formula) by the Role in the "Forecast" version (the "current" version), because the SELECT only applies to the Line ITEM FTEs but does not impact the Line Item "Role". You can see that behaviour in the picture below.

So the solution is either disable the "versions" for the Line Item "Role" in the source (provided that Role is not Version-specific)

Role with no versions.JPG or creating a staging module in between as stated before in the post. In the staging module replace Employees by Roles and use the first part of your formula: SUM. Then in the FInal Module use the other half of your formula: SELECT.

This should give you the desired solution.

I am sorry we kind of took a long way to end up with the same solution, but we gained better insights on how Anaplan formulas behave across multidimensional modules! 🙂

Cheers,

Alex.

All comments

ChrisAHeathcote

@LisaTonnerfors

You need to use a SUM SELECT combo in your SUM formula.

The following Anapedia link will guide you through how to do this;

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/SUM-SELECT.html

ChrisAHeathcote

Or if this doesnt work create a staging module where you create the mapping used in the sum for just the Budget version.

Use SELECT to pull through the required mapping just for BUDGET

LisaTonnerfors

Hi Chris!
I thought I was using the SUM SELECT combo?

'FTE Module'.'FTE'[SUM: 'FTE Module'.'HR-Role', SELECT: VERSIONS.Budget]

Are you referring to another formula?

Thanks!

/Lisa

adube

Staging module would be required.

AlejandroGomez

It seems like the SELECT is not doing what is expected of it. Try this:

Go to settings --> versions --> set your budget version as "current". ANaplan always retrieves data from current version by default when the target module has no versions.

So it should provide you with the right outcome. Please let us know.

EDIT: are you using switchover? if yes, on which period? The formula that you are using works for me when I do not use any switchover.

Cheers

Alex

www.olivehorse.com

LisaTonnerfors

@AlejandroGomez Problem is that Budget column was one example. I want to have a column for forecast and actual as well so I can't depend on which version is set to current version. I thought the SELECT: Versions.xxx would do it byt obviously not.
I'm not using switchover anywhere.

I'll try a staging module but spontaneously it feels like the problem will persist.

ChrisAHeathcote

Yes, I believe you are.
As others have suggested stage the calculation where you first select budget version and then from that module lookup...

AlejandroGomez

I see your point about having a Line Item per version.

Since I tried to replicate your case and seems to work fine in my case, I would suggest you to re-enter the formula one more time from scratch (now that switchover is disabled) and I would put the SELECT clause in first place, then the SUM, as the example from anapedia shows: https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/SUM-SELECT.html

Please, give it a shot and let us know.

LisaTonnerfors

@AlejandroGomez As a test, I created a separate module with only this line item to avoid any other distractions or settings that may affect. So the formula has been rewritten from scratch. I have tried both having SELECT before SUM and the the other way around. Switch over was never enabled.

When you tried this, did your module (the one with the formula) have versions? Because if I enable versions, it works fine for me. Once versions are removed from module, it takes the HR-Role value from version set to current version instead of the version I am using by SELECT.

Strange thing though that it takes correct FTE-value but sums on the wrong HR_Role?

AlejandroGomez

I have reviewed the modules that I created to replicate your case and found the issue:

The clauses SUM and SELECT in the formula in the "Target" module only applies to the "FTE" line item in the source module:

formula behaviour.JPG

So the solution is either disable the "versions" for the Line Item "Role" in the source (provided that Role is not Version-specific)

This should give you the desired solution.

I am sorry we kind of took a long way to end up with the same solution, but we gained better insights on how Anaplan formulas behave across multidimensional modules! 🙂

Cheers,

Alex.

LisaTonnerfors

@AlejandroGomez Thank you so much for claryfying what actually happens.
Problem is solved by a staging module!

AlejandroGomez

Happy to help!

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…