Summary Method for Lookup function

PalmHazel0531
PalmHazel0531
in Modeling

Hi,

 

What is the best workaround when using the Lookup Function and you need a Formula Summary Method?

 

Currently, Anaplan does not allow this and for some cases, especially using % accounts, it is best to use the Formula aggregation in order to get the right math at top level hierarchies and Time Summaries (i.e, All Periods). 

PalmHazel0531_1-1636039658521.png

Thanks!

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @PalmHazel0531 

     

    Give this a try, create another line item, call it Staging, where you get the summation or the value for the lookup and turn the summaries off.  In the line item you are wanting to use with the Formula summary, reference the Staging line item.

     

    Rob

Answers

  • thanasis
    thanasis
    You can create a stage module in order to split the calculations and get the data faster
  • ChrisAHeathcote
    ChrisAHeathcote

    Pull in both sides of the % into separate line items, set these to SUM and use RATIO as your summary method. Use the two new line items in the ratio settings.

  • shubhamrawat
    shubhamrawat
    edited May 2023

    Add an additional line item for staging, use it to grab the data from Lookup, set the summary for this to SUM, now reference this staging line item directly to the Final line item where you want the data and set the summary to formula for this line item. :)

  • kberger1
    kberger1

    This is great! I was able to use a line item to reference a prior year month (summary = none) then "lookup" the amount from that period (summary = sum) and pull into another module to get the summary to work with Formula, closing balance and get a variance for year over year with correct totals for my dimensions and hierarchies. - Thanks!

Categories