Performance Comparison - YEARVALUE(x) vs x[LOOKUP: FY]

Hi all,

Was hoping to get some insight into the performance implications of obtaining the year value of a line using either YEARVALUE(x) or x[LOOKUP: FY].

Both will retrieve the same result assuming summaries/timescales are setup correctly, but they are definitely not calculated the same (based on anecdotal tests). From what I can tell, YEARVALUE/MONTHVALUE (et al) appear to run significantly faster than their LOOKUP equivalent and the difference grows based on the granularity of the timescale.

I presume that using YEARVALUE triggers a calculation to occur within the same block vs LOOKUP requiring additional steps to find the value, but this doesn't appear to be documented anywhere (that I can find).

Was hoping someone had some insight to yay/nay my thoughts.

Cheers,

Accepted answers

jasonblinn

@luke_e I would say that your analysis is spot on.

I recently had an older model reviewed by the Hypercare team at Anaplan and one of the biggest takeaways is that the line items were using a lookup on time vs using Yearvalue() etc were some of the biggest negative impacts on the model open time analysis, and luckily are some of the easiest ones to fix!

I believe that your take on 'why' is accurate as well, instead of needing to go elsewhere to find what data it needs to pull it is able to use a direct reference with the Yearvalue, etc all within the Time Block.

Jason

All comments

jasonblinn

@luke_e I would say that your analysis is spot on.

Jason

AntonMineev

@luke_e Thanks for the raised topic!

@jasonblinn @luke_e Do you think it is generally true that specific formulas are faster than "general" formulas?

For example, that YEARTODATE will be faster than CUMULATE and even option PREVIOUS(LI)+LI (plus IF to reset the year).

I know that Anaplan uses separate blocks for time calculations. This certainly speeds up the CUMULATE formula, but in the choice between YEARTODATE and PREVIOUS(LI)+LI, the answer is not so obvious.

MarkWarren

Yes! Formulas like YEARTODATE are designed to work in a very specific way, in essence they know what dimensions they're going to operate on and how they're going to calculate on that dimension.

Taking CUMULATE vs PREVIOUS as you've raised, I have this useful diagram to help explain how they work.

The arrows represent how the function is looking at other cells to work out what it needs to calculate the value. CUMULATE will look at all cells, which means the arrows in red are redundant, it decides it does not need those values at this position, but it has to check.
PREVIOUS only looks at the previous value. (I know, didn't really need to explain that one!)
The formula to mimic CUMULATE just pulls from the source cell and the previous cell, so that's 2 "bits of work" compared to the 6 that CUMULATE does.

LOOKUPS have to... look up the data 😂 but to do that the function needs to understand the relationship of the data, what is this mapping, what does it relate to, what's the difference between my source and target cells.

So when considering the use or a certain function you have to try to think about how that may work, the more "generic" the function the more it may have to do in working out the calculation (like LOOKUP can be used with so many variations of dimensions for example).

The fairly recent update to LEAD and LAG to have a STRICT parameter, highlights this too. This was added to improve performance, so when using it with STRICT, LAG will only apply to the past and will have less dependencies to other cells.

AntonMineev

@MarkWarren Thank you very much for your reply!
Does this mean that function YEARTODATE will work the same way as "previous" construction (let's say for a time of 1 year, without going through a year), because to calculate they need to "look" the same amount of cells?

MarkWarren

Yes, very similar, it knows to work along the time dimension in a specific order.

luke_e

Appreciate the responses @jasonblinn @MarkWarren .

Glad to hear that my peformance metrics weren't lying, hah.

But agreed, fortunately the majority of our x[LOOKUP: FY] usage can be quickly updated using the line item extract except where there's additional parameters involved (which in our scenario is minimal).

Thanks again,

devrathahuja

Hi @AntonMineev ,
Just saw in 1 of the recent MAPS report the total calculation time was significantly lower for IF + Previous combination than the yeartodate function.

You could also try it out by creating both the test line items in a huge module & then evaluating its impact in the MAPS report.

Also found this article that YTD is a variation of cumulate - https://community.anaplan.com/discussion/159398/convert-cumulate-functions-to-previous-model-optimization-team-series

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
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…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…