Driver formula help

Hi,

I am hoping someone can help explain / walk me through what this driver formula is doing.

Driver Method <> 'SYS002 - Model Lookups'.'Flat to PY - 70% Owned Sales'

THEN

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.

Screenshot_2022-06-22_225554_140334.jpg

Accepted answers

ChrisStauffer

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.

All comments

ChrisStauffer

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

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

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…