Value from wrong version is returned

LisaTonnerfors
New Contributor

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.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AlejandroGomez
Master Anaplanner/Community Boss

Re: Value from wrong version is returned

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.

 

solution.JPG

 

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.JPGor 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.

 

 

View solution in original post

12 REPLIES 12
ChrisHeathcote
Community Boss

Re: Value from wrong version is returned

@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 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
ChrisHeathcote
Community Boss

Re: Value from wrong version is returned

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
Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
LisaTonnerfors
New Contributor

Re: Value from wrong version is returned

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
Occasional Contributor

Re: Value from wrong version is returned

Staging module would be required.

ChrisHeathcote
Community Boss

Re: Value from wrong version is returned

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

Gold Partner and Regional Partner of the Year 2021, EMEA
AlejandroGomez
Master Anaplanner/Community Boss

Re: Value from wrong version is returned

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.

AlejandroGomez_0-1601372904814.png

 

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
New Contributor

Re: Value from wrong version is returned

@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.

 

AlejandroGomez
Master Anaplanner/Community Boss

Re: Value from wrong version is returned

 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
New Contributor

Re: Value from wrong version is returned

@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?