LOOKUP not behaving as expected on production list

cbrookes · 2023-12-07T21:57:50+00:00

There was an error rendering this rich post.

Hi all,

I'm trying to understand why using LOOKUP on custom versions from a SYS Constants module isn't behaving the same as SELECT.

I recently converted all of our custom version lists to production lists to help save space in the DEV/UAT model. As part of that change, I updated all SELECT statements referencing custom versions to use a LOOKUP from that version in a SYS Constants module.

Example:

Formula = MRR[LOOKUP: SYS Constants.Forecast]

FY24 MRR Forecast Version = 100

FY24 MRR Alternate Scenario = 90

What could be causing the Alternate Scenario MRR to be different from the Forecast MRR when the formula is looking up the Forecast version? I found a solution on the forums (see screenshot) that fixed the issue for me, but I'm not using list subsets with our versions so the explanation as to why it fixes the issue doesn't make sense to me.

Screenshot 2023-12-07 at 12.30.02 PM.png

My goal is to understand when a LOOKUP in a SYS Constants module will suffice versus needing to put the LOOKUP line item in the corresponding SYS List module.

Accepted answers

All comments

CommunityMember126793

Hi, not so clear on the error message you were receiving whilst applying LOOKUP in place of SELECT.. but Anaplan won't allow the following

Specific item to be referred from PROD list.. be it SELECT or LOOKUP..

andrewtye

Hi @cbrookes - i don't know why it doesn't pull based on the direct lookup but am sure @AnaplanOEG or @rob_marshall might be able to shed some light on it.

As you say need to have a line item to reference against per the below. It's probably a bit like why you can't have lookup in line item and also have summary setting as formula. It is somewhat frustrating

Hope the answer is easy to understand (from OEG that is!)

Andrew

rob_marshall

@cbrookes

It would be easier to understand and explain it if we could see your formulas and what you are attempting to do.

cbrookes

Thanks for the help @rob_marshall and @andrewtye! I want to create a line item that is always equal to the MRR in the forecast version (custom version) both line items are in the same module with the same dimensionality.

Module: CALC Rep Driven MRR

CALC Rep Driven MRR Dimensionality: Region L3, Segment L2, Segment Role L2, Tenure L2, Custom Version, Time (with all summaries). All lists besides the custom version are subsets.

Source Line Item: CALC Rep Driven MRR.MRR (SUM summary)

Target Line Item: CALC Rep Driven MRR.MRR Forecast (SUM summary)

Lookup Line Item that doesn't work: SYS Constants.Forecast Version (Format = Custom Version, No dimensions, and no summary)

Target Line Item Formula that doesn't work: IF Historical Period? THEN DAT MRR.MRR[SUM: all dimensions] ELSE CALC Rep Driven MRR.MRR[LOOKUP: SYS Constants.Forecast Version]

Lookup Line Item that works: SYS Custom Version.Forecast Version (Format = Custom Version, Custom Version dimension, and no summary)

Target Line Item Formula that works: IF Historical Period? THEN DAT MRR.MRR[SUM: all dimensions] ELSE CALC Rep Driven MRR.MRR[LOOKUP: SYS Custom Version.Forecast Version]

cbrookes

@CommunityMember126793 I was not receiving an error message. I switched my SELECTS for LOOKUPS when I converted the list to production to avoid the error you are referring to. While testing I noticed some of the summary calculations were not what I was expecting. I was able to find a work around to fix the solution, but I'm looking to understand why the work around fixes the issue.

CommunityMember126793

@cbrookes seems your issue is fixed. that's good to know but can u please provide few screen-prints, if possible, in order to understand your query further i.e. "why the work around fixes the issue."..

Quick Links

Can you help?

Unanswered questions

Not Able to Join the Level 1 Model Building Course
Hey Everyone, I was trying to learn about the Level 1 Model Building Course, when i log in to my system and click on "Start the course" option its showing training unavailable, Can anyone please help me with this @Dikshant and all?
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…