copy between versions - current year to next year

Is there an easier or faster way to copy data from FY21 Forecast version to FY22 plan version without copying it module by module? Also, I need to keep the current FY21 plan intact. so the result on plan version should be FY21 plan + copy of FY22 Forecast. This is a model level change.

 

Lag function doesn't work on version formula if you have summary enabled on line items. I have a huge model with over 400 modules. Any ideas?

 

Answers

  • @himanshu.badhai 

     

    There are two ways thru which we can change/update the data at model level i.e., Bulk Copy and Version Formula 

     

    Bulk Copy would have done the Job, if it was only copying the data. But it is basically merging/combining the data of two different versions. Hence won't apply in this case

    Another way was to have a formula written in Version formula editor 

     

    Edited: As a matter of fact - you can mention Years in Version Formula provided there are certain prerequisites maintained. You may have to hardcode it and later you can remove the formula and your numbers will remain intact

    Create a New Version and write the formula in it

    Misbah_0-1634194789668.png

    Misbah_1-1634194857120.png

     

    Misbah

  • @Misbah awesome! awesome! awesome!

     

    I haven't tried it on the actual model. But I did a POC and it seems to be working

     

    Involves some hard-coding but will do the job.

     

    Step-1: create a new version "Next Year Plan" and write this formula-

     

    IF ITEM(Time) < TIME.'Jan 22' THEN Plan ELSE IF ITEM(Time) = TIME.'Jan 22' THEN Forecast[SELECT: TIME.'Jan 21'] ELSE IF ITEM(Time) = TIME.'Feb 22' THEN Forecast[SELECT: TIME.'Feb 21'] and so on...

     

    step-2: bulk copy "Next Year Plan" to Plan

     

    Thank you for your help.

  • Unfortunately this solution will only work if all of your modules use Time and version dimension. It throws error for modules that use version as a dimension but not Time. Modules without version and Time are fine.

  • @himanshu.badhai 

     

    That's exactly what I was talking about. If we are referencing Time in Version formula then that is the pre requisite. 

     

    I wish I had better response for you.

     

    Misbah