LOOKUP not behaving as expected on production list

edited December 2023 in Modeling

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.


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.

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.



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

  • 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!)


  • @cbrookes

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

  • cbrookes
    edited December 2023

    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]

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

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