requires cell info loaded in particular year

imerchant
imerchant
in Modeling

Hi All,

 

I am trying to write an formula which should plot "year" info, where it was loaded in a list. This is about dates we manually loaded in a particular year. Now i want to extract the year info on the cell where this was loaded and not the year that the date mentions. For example we loaded 31-Dec-2018 in Year "2019" in the Year* list, where this data was loaded. When i type below formula, "Year[Select: Time.'Current Period']", system gives current year, i.e. 2018. When i write YEAR(START()), system again plots "2018". When i write FINDITEM('Year*', TEXT(Year)), system again gives 2018. 

 

My question is how to get 2019 as year on a cell where the data was loaded?

 

Any help from you experts holds high value to me. Thanks!

Tagged:

Answers

  • Ethan_Rold1
    Ethan_Rold1

    Can you load a screen shot into your comment showing where you are trying to do this? A couple of items that I have picked up from your post:

     

    1) YEAR(), START() or SELECT:Time.Current Period are both default time functions that are going to pick up the time scale that you have set in your model. If you have a module with Time included as a dimension it will use these functions to pick up your time settings.  It appears that the current period in your model time settings is set to 2018 and it won't reflect 2019 until that year is in the model.

    2) I assume you created a year list in the model and have references where you put 31-Dec-2018 as a child to 2019 in a hierarchy. Your FINDITEM is only going to work to match a text formatted value - property or line item - to a list member. I assume in your situation that you have a year field that creates a numeric reference on a portion of the time (guessing that year is pulled out as a number field as the third part of a date part) and so when you do finditem it is transforming the number 2018 as the year. Instead you might be able to use your FINDITEM but leverage a PARENT(FINDITEM('Year*',"31-Dec-2018")) to look at the correct year.

     

    I am making a lot of assumptions in my answer but hopefully it gets you closer. Feel free to load some more detail or a screen shot to the thread and I may be able to help further.

  • imerchant
    imerchant

    I have manage to get the year FY19 with parent(item(time)) formula but i wanted "2019" instead of FY19. 

    I tried to use left and right syntax but it only works on text format lines however the data i am picking from is date (year) format. What should be the right formula to pick and convert date (year) line into text line with left and right syntax?

     

    i tried using a few formulas but gave me errors. Any help is appreciated.

  • rob_marshall
    rob_marshall

    @imerchant 

     

    You can use Year(item(time)) and that should work for you.  This returns the number.  If you want this as text, you can format the line item as text and the formula would be TEXT(YEAR(ITEM(Time))).

     

    Thanks,

     

    Rob

Categories