Summary Method for Lookup function




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



Best Answer

  • rob_marshall



    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.




  • You can create a stage module in order to split the calculations and get the data faster
  • 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
    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. :)

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