Driver Calc help with logic

Walker32
Walker32
in Modeling

I have a driver called "set to plan" which is just assigning the planned amounts to the forecast periods, but this is only happening for 2022 forecast periods.

 

The previous driver Calc:

IF
    Driver Method <> 'SYS002 - Model Lookups'.Set to Plan
THEN
    0
ELSE
    'REP001 - Balance Sheet (Reporting Output)'.Amount[LOOKUP: 'SYS006 - Version Management'.Associated Plan]

 

Ideally what I want the logic to do is if the Current Year > than Year of the Switchover Period (May22) then use the PY period's value. 

 

To start I added a Switchover Period(Year) line item to the sys006 module so i can refer to it. and this is the change I made to the formula:

IF
	Driver Method <> 'SYS002 - Model Lookups'.Set to Plan
THEN
	0
ELSE
	IF
		'SYS006 - Version Management'.Current Year > 'SYS006 - Version Management'.'Switchover Period (Year)'
	THEN
		LAG('Total Output ($) [Staging]', 12, 0)
	ELSE
		'REP001 - Balance Sheet (Reporting Output)'.Amount[LOOKUP: 'SYS006 - Version Management'.Associated Plan]

 

however the values for FY23 are still showing up as 0. What am I missing?

 

Attached the screenshots of the Modules CAL001 Periods (Feb22, May22, Feb23) and SYS006(blueprint and view) for reference.

Tagged:

Answers

  • JensitSebastian
    JensitSebastian

    Hi @Walker32 ,

     

    For the updated formula once you have filled in 'SYS006 - Version Management'.'Switchover Period (Year)'= PARENT('SYS006 - Version Management'.'Switchover Period (Month)); you would get FY23. 

     

    Ideally I would have compared these with a SYS Time module items. Like build a "SYS Time" module with Time as dimension. Time Rage can be Model Calendar or Otherwise and Time Scale Month. 

    Then you can identify each month with SYS Time.Month=Period(START()) formula.

    Use a Boolean to check if the Period is greater than the switchover month. If greater then pick the previous year same month value. For that  formula will be SYS Time.PY Same Month =  SYS Time.Month-12. And use SYS Time.PY Same Month lookup in the formula.

     

    Thanks,

    Jensit

  • ankit_cheeni
    ankit_cheeni

    Hi @Walker32  

     

    Maybe I am completely lost but the screenshot you shared isn't of the calculation you are referring to. 

    Also, I see you neither have a formula for Switchover Period (Year) not any value entered into it. Did you enter values when you were hoping it to get values for 2023?

  • JamesPreston
    JamesPreston

    My first check would be that the dimensioning of 'SYS006 - Version Management' is correct and the values of Current Year and 'Switchover Period (Year)' are non-zero

     

    My final thought is that Current Year is derived from Current Quarter and Current Quarter is derived from Switchover Period (Month) - is this the indented behavior?

     

    - James

  • Walker32
    Walker32
    Hi Ankit.

    CAL001 is the calulation module it references the REP001 value and outputs it.

Categories