Year values to months




For an input screen, I want to enable users to input at a yearly level and then I want that same value to flow to every month pertaining to that year automatically.  This is to make life easier for the end user to not input data at a monthly level, rather do one input at the year. 


Example. Value for FY 20:- 1000. Then Jan-dec 20 would have 1000 each.


I created an input screen by year with same dimension as that of the month module and now I wish to create an action to take data from year module to that of month module. However the mapping stops me from directly copying over data. 


I didn't go the formula route because I want the end user to still have the ability to edit if they wish to make changes to the numbers. Can this be done in the way above that is receive input at yearly level and then disaggregate the numbers. 



Best Answer

  • KirillKuznetsov

    Hi @Sachinsourav02 

    What if you go with formula way, but add another line item for overwrite?

    It will be much cleaner picture having initial data and below the edited line.



    Year base10001000100010001000    











    Note: if you need to zero down - most common approach is to make an if statement where 0.00001 = 0. Or just an additional boolean line item (as in May on the table)


    If you still want the import. You will have to use "the formula way" anyway, but as a precalc line item dimensioned by months. From this line item you will do the import.

    But this way you will have to consider if anyone would ever want to change this initial year value and push the button to re-write monthly - it may cause dataloss of the overwrites made by others.

    So taking in account the above, I would prefer the first option with overwrite line.



  • Hi @Sachinsourav02!

    You need to make a separate formula for copying from the year and LI adjustments.


    Снимок экрана 2022-01-18 в 16.28.31.pngСнимок экрана 2022-01-18 в 16.28.37.png

  • @Sachinsourav02  AS @KirillKuznetsov already mentioned, even it will be possible to create import actions to copy data from total year to months, I would strongly NOT advise to use the import action.


    It will be difficult to make a distinction between which rows should "be needed to launch the import" and which rows are OK with the manual monthly modifications. There will always be potential issues that Total Year values will not match with monthly values. 

    Also, a user could "forget" to launch the import actions... :)


    Added to the solution proposed by @KirillKuznetsov you could also consider the option to create 1 module with month and activate the "Breakback" option. 

    In this case the Total Year and month amounts are manually input. 


    The user can choose to input data at TOTAL year (but using an Yearly amount) and automatically the total year will be allocated to months based on the existing monthly values or will be equally split if no values are input. 

    So, in your case the user should input 1200 at total year level in order to have 1000 per every month, and in a second step, he/she can go and adjust the monthly amounts. 


    Hope it helps


  • @alexpavelThanks for your comments, I also thought about Breakback, good catch!

  • Hi @Sachinsourav02 


    You can use the yearvalue() function , this function takes year value and puts it across months of the year , 


    User input in years


    In Months all months in FY18 show FY18 value and all months in F19 shoe F19 yearvalue