Lock Lookup. Possible?

Arth
Arth
in Modeling

Hi everyone is there any possibility to lock the lookup function when using offset? what i want to achieve is when i change my time period, the awarded will take the current period in module Construction Forecast-Awarded and will be place in Awarded column. same thing in Awarded 2nd Month, it will display the second month. so i tried the OFFSET function but unfortunately the dimension month will move also to the next month.

image.png

i have a module which my dimension is time

image.png

on my Awarded Column, this is my formula:

image.png

and on my Awarded 2nd month, this is my formula:

image.png

Answers

  • AjayM
    AjayM

    If I am not wrong, you want model's current period setting to be locked as current month for 'Awarded', and corresponding 2nd month as the subsequent month.
    If you always want 'current period' value to be fetched into 'Awarded' lineitem, why do you need time dimension to that lineitem? Same applies to 'Awarded 2nd Month' lineitem.

  • seymatas1
    seymatas1

    Hi @Arth,

    The question isn’t clear to me. Can you make it a bit more understandable? For example, you can create a line item called "Expected Result" and type the number you expect to see in the cell.

    Also, instead of using OFFSET, you can create another line item called "Previous Month to Lookup" and use LOOKUP on that line item. OFFSET is calculation-heavy, so it's better to avoid it if possible.

    If you need to offset only by one month, consider using the PREVIOUS() or NEXT() functions instead.

    I also noticed that your "Date to Lookup" and "Month to Lookup" line items both have "month", once you finish your development, you should delete one to keep things simple.

    Lastly, try to put your lookup line items in a smaller system module for better model performance.

    I hope this helps!
    Şeyma 🌷🙂

Categories