Non Standard Allocation in SAC

[SAC_Newbie09]

Hi All,

We are trying to create non-standard allocation logic via Advanced formula step. Any suggestions on How we can configure this via advanced formula or standard allocation or mix of both. 

Existing mapping between source and driver account data

ITEM CODE	Diver account
SL1000	AAC1
SL3000	AAC2
SL8000	AAC3
SL1000	AAC4

Input data

AUDITTRAIL

VERSION

TIME

COMPCODE

ACCOUNT

COSTCENTER

ITEM_CODE

COLLECTION

CHANNEL

COUNTRY

BRAND

GENDER

PRODUCT

Amount

DT01

BUDGET

202412

CC01

AAC5

COSTC1

SL1000

#

2

#

#

#

#

200.00

Driver based on existing mix %

AUDIT TRAIL

VERSION

TIME

COMP CODE

ACCOUNT

COSTCENTER

ITEM_CODE

COLLECTION

CHANNEL

COUNTRY

BRAND

GENDER

PRODUCT

Amount

Existing mix  Driver%

DT02

BUDGET

202412

CC02

AAC1

SHOP1

SL2000

DM1M

2

US

DM1

M

10

1,000.00

0.25

DT02

BUDGET

202412

CC02

AAC4

SHOP1

SL2000

DM1M

2

US

DM1

M

20

500.00

0.125

DT02

BUDGET

202412

CC02

AAC1

SHOP1

SL2000

DM1M

2

US

DM1

M

30

2,500.00

0.625

DT02

BUDGET

202412

CC02

AAC2

SHOP1

SL2000

DM1M

2

US

DM1

M

40

3,500.00

1

Output after applying Driver to input Data

FINAL RESULT

AUDIT TRAIL

VERSION

TIME

COMP CODE

ACCOUNT

COSTCENTER

ITEM_CODE

COLLECTION

CHANNEL

COUNTRY

BRAND

GENDER

PRODUCT

Amount

DT03

BUDGET

202412

CC01

AAC5

COSTC1

SL1000

DM1M

2

US

DM1

M

10

50

DT03

BUDGET

202412

CC01

AAC5

COSTC1

SL1000

DM1M

2

US

DM1

M

20

25

DT03

BUDGET

202412

CC01

AAC5

COSTC1

SL1000

DM1M

2

US

DM1

M

30

125

200.00

[JefB · Accepted solution]

I don't know why your product 40 is not in the output, but this could be the code for the allocation to work

MEMBERSET [d/Measures] = "Amount"

AGGREGATE_DIMENSIONS = [d/COMPCODE], [d/ACCOUNT], [d/COSTCENTER], [d/ITEM_CODE]
AGGREGATE_WRITETO [d/COMPCODE] = "CC01"
AGGREGATE_WRITETO [d/ACCOUNT] = "AAC5"
AGGREGATE_WRITETO [d/COSTCENTER] = "COSTC1"
AGGREGATE_WRITETO [d/ITEM_CODE] = "SL1000"

VARIABLEMEMBER #TOTAL OF [d/AUDITTRAIL]
DATA([d/AUDITTRAIL] = #TOTAL, [d/COLLECTION] = "#", [d/COUNTRY] = "#", [d/BRAND] = "#", [d/GENDER] = "#", [d/PRODUCT] = "#") = RESULTLOOKUP([d/AUDITTRAIL] = "DT02") 

DATA([d/AUDITTRAIL] = "DT03") = (RESULTLOOKUP([d/AUDITTRAIL] = "DT02") / RESULTLOOKUP([d/AUDITTRAIL] = #TOTAL, [d/COLLECTION] = "#", [d/COUNTRY] = "#", [d/BRAND] = "#", [d/GENDER] = "#", [d/PRODUCT] = "#")) * RESULTLOOKUP([d/AUDITTRAIL] = "DT01", [d/COLLECTION] = "#", [d/COUNTRY] = "#", [d/BRAND] = "#", [d/GENDER] = "#", [d/PRODUCT] = "#")

[SAC_Newbie09 · Accepted solution]

@JefB Thank you for your reply. This looks more appropriate with our requirement but missing one important point here that there is a mapping which defines which ACCOUNT driver data can be used for an ITEM CODE. What we want to know how can we define this mapping in SAC and then utilize it in DA. This is the reason PRODUCT 40 was not considered because it is not one of the reference ACCOUNTs for SL1000. PS: DRIVER data is picked up based on reference ACCOUNT for ITEM CODE in source data. Collection/Channel/Country/Brand/Gender/Product are the dimensions to be used for allocation. Rest others from driver data can be ignored.

[JefB · Accepted solution]

Ok, then just add ITEM_CODE property to ACCOUNT dimension. Make sure to include AAC5 with SL1000 mapped.
AAC1 SL1000
AAC2 SL3000
AAC3 SL8000
AAC4 SL1000
AAC5 SL1000
Then add this line in MEMBERSET and it should do what you want.
Not sure about maintainability. SAC is not so good in further advanced property selections & re-usability of all of this. 🙂

MEMBERSET [d/ACCOUNT].[p/ITEM_CODE] = "SL1000"

[SAC_Newbie09 · Accepted solution]

It's probably the same we wanted to do but the relationship between ACCOUNT and ITEMCODE N:N something like below.

AAC1 SL1000

AAC2 SL1000

AAC2 SL1001

AAC4 SL1003

AAC5 SL1003

AAC5 SL1005

AAC6 SL1006

[JefB · Accepted solution]

Yeah, SAC is really not good enough for such N:N relationships. You are forced into restricted workarounds or remodeling of your plan data in unnatural ways.