Budget Lookback Logic

[former_member640817]

Forgot some vitally important info in my initial post that I have added based on this guide (sorry about that!).

I am trying to create logic that calculates orders. It works well already - but where I'm stuck is creating a lookback to a different category.

What I want to do is have the 2020.W01 (TIME_WEEKLY) ORDERS (OSB_ACCOUNT) BUDGET (CATEGORY) calculation lookback to 2019.W52 ACTUAL BACKLOG and work for future years as well.

Version:

Calc Engine: JAVASCRIPT (tested script in guide and it validated)

Model: OSB Model is used to calculate orders, shipments, and backlog.

Dimensions in Model:

In the OSB_ACCOUNT dimension there are members BACKLOG (AST) ORDERS (EXP) PPBACKLOG (AST) SHIPMENTS (EXP)

We currently do not use PPBACKLOG - it could be used for this calc. I'm just not sure how to do the calc.

Purpose: To run as default.lgf

Describe Calc Logic:

Orders = Current Week Backlog + Shipments - Previous Week Backlog

The issue for this post is to have the ORDERS calc for 20XX.W01 BUDGET of every year use 20XX.W52 or 20XX.W53 ACTUAL as the Previous Week Backlog.

Data Sent by User (each week):

-Current Week Shipments

-Current Week Backlog

Current script below (without lookback). I'm not sure where to begin for this lookback.

//Scope for Orders Calc
*XDIM_MEMBERSET OSB_ACCOUNT = SHIPMENTS,BACKLOG
*XDIM_MEMBERSET TIME_WEEKLY = %TIME_WEEKLY_SET%,TMVL(-1,%TIME_WEEKLY_SET%)
*XDIM_MEMBERSET AUDITTRAIL = <ALL>
*XDIM_MEMBERSET RPTCURRENCY = LC,USD,EUR

// Calculates Orders
 *FOR %TIMES% = %TIME_WEEKLY_SET%
	*WHEN TIME_WEEKLY
		*IS %TIMES%
		*WHEN OSB_ACCOUNT
			*IS SHIPMENTS
			*REC(OSB_ACCOUNT = ORDERS)
			*IS BACKLOG
			*REC(OSB_ACCOUNT = ORDERS)
		*ENDWHEN
		*IS TMVL(-1,%TIMES%)
		*WHEN OSB_ACCOUNT
			*IS BACKLOG
			*REC(FACTOR = -1,TIME_WEEKLY = %TIMES%,OSB_ACCOUNT = ORDERS)
		*ENDWHEN
	*ENDWHEN
*NEXT
*COMMIT

[former_member186338 · Accepted solution]

Do you have some property containing unique value for all W01? May be PERIOD: W01?

Use it in the script:

*LOOKUP OSB //NameOfCurrent Model
*DIM OSB_ACCOUNT="BACKLOG"
*DIM TIME_WEEKLY=TIME_WEEKLY.PREVWEEK
*DIM PRWEEK:CATEGORY="BUDGET"
*DIM PRWEEKFIRST:CATEGORY="ACTUAL"
*ENDLOOKUP

*WHEN CATEGORY
*IS BUDGET //Only for Budget data
*WHEN OSB_ACCOUNT
*IS SHIPMENTS
*WHEN TIME_WEEKLY.PERIOD
*IS W01
*REC(EXPRESSION=%VALUE%+[OSB_ACCOUNT].[BACKLOG]-LOOKUP(PRWEEKFIRST),OSB_ACCOUNT = ORDERS)
*ELSE
*REC(EXPRESSION=%VALUE%+[OSB_ACCOUNT].[BACKLOG]-LOOKUP(PRWEEK),OSB_ACCOUNT = ORDERS)
*ENDWHEN
*ENDWHEN
*ENDWHEN

*WHEN CATEGORY
*IS BUDGET //Only for Budget data
*WHEN OSB_ACCOUNT
*IS BACKLOG
*WHEN TIME_WEEKLY.PERIOD
*IS W01
*REC(EXPRESSION=%VALUE%+[OSB_ACCOUNT].[SHIPMENTS]-LOOKUP(PRWEEKFIRST),OSB_ACCOUNT = ORDERS)
*ELSE
*REC(EXPRESSION=%VALUE%+[OSB_ACCOUNT].[SHIPMENTS]-LOOKUP(PRWEEK),OSB_ACCOUNT = ORDERS)
*ENDWHEN
*ENDWHEN
*ENDWHEN

Hope it's clear!

[former_member186338]

In TIME_WEEKLY dimension create a property PREVWEEK and fill it for each base member with corresponding previous week member.

Then use the following script (designed for default.lgf!):

*LOOKUP OSB //Name Of Current Model
*DIM OSB_ACCOUNT="BACKLOG"
*DIM PRWEEK:TIME_WEEKLY=TIME_WEEKLY.PREVWEEK
*ENDLOOKUP

*WHEN OSB_ACCOUNT
*IS SHIPMENTS
*REC(EXPRESSION=%VALUE%+[OSB_ACCOUNT].[BACKLOG]-LOOKUP(PRWEEK),OSB_ACCOUNT = ORDERS)
*ENDWHEN

*WHEN OSB_ACCOUNT
*IS BACKLOG
*REC(EXPRESSION=%VALUE%+[OSB_ACCOUNT].[SHIPMENTS]-LOOKUP(PRWEEK),OSB_ACCOUNT = ORDERS)
*ENDWHEN

Please read my blog: https://blogs.sap.com/2014/06/09/how-to-write-defaultlgf/

[former_member186338]

P.S. The only issue I see:

If the user will change the value not in the latest week, then all weeks after change have to be recalculated.

Can be done with DM script.

[former_member640817]

That's much cleaner! Thanks for the blog - really helpful.

What about a lookback on 2020.W01 BUDGET grabbing 2019.W52 ACTUAL. Or at 2021.W01 BUDGET looking back to 2020.W53 ACTUAL?

[former_member186338]

jdevries192

"What about a lookback on 2020.W01 BUDGET grabbing 2019.W52 ACTUAL. Or at 2021.W01 BUDGET looking back to 2020.W53 ACTUAL?"

Sorry, but not clear!

Please provide test case...

[former_member186338]

jdevries192

P.S. If you are talking about lookup to previous year week - then no issue. Lookup will use property! And the member contained in this property.

Please accept the correct answer.

[former_member640817]

Lookup previous year week in a different category only when BUDGET 20XX.W01 is loaded.

Example: When a user saves BUDGET data in 2020.W01 the current logic would use BACKLOG 400,000.

The goal is when the user saves BUDGET category in 2020.W01 only that the logic would grab 420,000 (ACTUAL BACKLOG) - not 400,000 (BUDGET BACKLOG).