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.

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,

L

Tagged:

@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

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

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?

• Yes, very similar, it knows to work along the time dimension in a specific order.
• 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,

L