Driver formula help

Walker32
Occasional Contributor

Driver formula help

Hi, 
 
I am hoping someone can help explain / walk me through what this driver formula is doing. 
 
IF
    Driver Method <> 'SYS002 - Model Lookups'.'Flat to PY - 70% Owned Sales'
THEN
    0
ELSE
    LAG('Total Output ($) [Staging]', 12, 0) * 0.3 + LAG('Total Output ($) [Staging]', 12, 0) * 0.7 * 'DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0))
 
My thought process:
-  Taking prior years month (may-21) and multiplying by .3
LAG('Total Output ($) [Staging]', 12, 0) * 0.3 + LAG('Total Output ($) [Staging]', 12, 0) * 0.7 * 'DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0))
 
- adding the value of prior years month (may-21) multiplied by .7 multiplied by Current month's total owned sales 
LAG('Total Output ($) [Staging]', 12, 0) * 0.3 + LAG('Total Output ($) [Staging]', 12, 0) * 0.7 * 'DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0))
 
- dividing by the difference of Current month's total owned sales (may22) and Prior month's (May21) total owned sales.
LAG('Total Output ($) [Staging]', 12, 0) * 0.3 + LAG('Total Output ($) [Staging]', 12, 0) * 0.7 * 'DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0))
 
However I am not getting the same value that the model is showing (associated ROF). Attached screenshot for reference.
 
Please let me know what I am missing here.
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisStauffer
Certified Master Anaplanner

Hi Walker,

 

Sometimes it helps to build a calc in pieces then consolidate and delete extra line items.

 

LAG('Total Output ($) [Staging]', 12, 0) * 0.3 = 22,075,640    (73,585,465 * .3)

+

LAG('Total Output ($) [Staging]', 12, 0) * 0.7 = 51,509,826    (73,585,465 * .7)

*

DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0) =  20.    (1,702,202 / 1,702,202 - 1,615746)

 

then I get 1,014,158,971 when I multiply 51,509,826 * 20

 

when I add 1,014,158,971 to 22,075,640,  I get 1,036,234,610 which is close to your number so i assume I'm off by rounding.

 

 

View solution in original post

2 REPLIES 2
ChrisStauffer
Certified Master Anaplanner

Hi Walker,

 

Sometimes it helps to build a calc in pieces then consolidate and delete extra line items.

 

LAG('Total Output ($) [Staging]', 12, 0) * 0.3 = 22,075,640    (73,585,465 * .3)

+

LAG('Total Output ($) [Staging]', 12, 0) * 0.7 = 51,509,826    (73,585,465 * .7)

*

DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0) =  20.    (1,702,202 / 1,702,202 - 1,615746)

 

then I get 1,014,158,971 when I multiply 51,509,826 * 20

 

when I add 1,014,158,971 to 22,075,640,  I get 1,036,234,610 which is close to your number so i assume I'm off by rounding.

 

 

Walker32
Occasional Contributor

Thank you! I see where I tripped up..order of operations.