Join 2 data subsets in SAC Advanced Formula

[igladyshev]

I have 'Amount' Measure related to 'Location from', 'Location to' and 'Date' Dimensions, and I want to "look up" corresponding distance between 2 locations and multiply Amount by Km using Advanced Formula script. Distances are time-independent and input-ready, so I must store them in a planning model.

If I use simple DATA([d/Measures] = "Amount*Km") = RESULTLOOKUP([d/Measures] = "Amount")*RESULTLOOKUP([d/Measures] = "Km", [d/Date]="") it doesn't return anything, as it seems it is required to directly filter all dimensions in the 2nd RESULTLOOKUP expression to make it work.

If I use foreach on 'Location from', 'Location to' combination + if condition it doesn't work as I can't refer to a dimension member in a loop due to integer/float variables allowed only

If I store 'Location from', 'Location to' and 'Km' in a separate model and use LINK to link this model (also I need to keep the same Date Granularity in a linked Model for this), system tries generate all combinations for all not filtered dimensions members in DATA expression if I do

DATA([d/Measures] = "Amount*Km") = RESULTLOOKUP([d/Measures] = "Amount")*LINK([Linked_Model] , [d/Measures] = "Km", [d/Version] = "public.Actual", [d/Date]="")

So it either returns incorrect results or simply fails with an error that there are too many combinations...

What would be an appropriate approach to perform such simple "joins" of data subsets in SAC advanced formulas?

[N1kh1l · Accepted solution]

igladyshev

igladyshevIf both Amount and KM are in same model within same version, it should be a simple Advanced Formula as your approach 1. You have to be careful about other dimensions in the model for multiplication. The value for other dimensions should be same for both Amount and KM to work as per lookup. See an example below.

Output: Other dimensions are # for both Amount and KM. I do not have # in my Date dimension so I have used 202301 (2023 Jan ) to plan my KM. For Location From and Location to I have used my Sales Org and Entity Dimensions

Advanced Formula: Please adjust the dimension and member names as per your model

MEMBERSET [d/SAP_CEP_SALESORG]=("L1","L2","L3") // Change to Location To as per your model
MEMBERSET [d/MEASURE]=("Amount","KM")
MEMBERSET [d/CALMONTH]=("202301","202302","202303")
DELETE([d/MEASURE]="Amount*KM")
DATA([d/MEASURE]="Amount*KM") = RESULTLOOKUP([d/MEASURE]="Amount") * RESULTLOOKUP([d/MEASURE]="KM",[d/CALMONTH]="202301")

The only reason this may not work is that your value for other dimensions for Amount and KM are not same.

Hope this helps !!

Please upvote/accept if this helps.

Nikhil

[William_Yu]

I'm not sure if there are other dimensions in your model other than Date, Location from and Location to. Most likely your really data points are not only on those three dimensions you listed, just keep in mind, advance formula always operates on the full granular dataset.

[igladyshev]

You are absolutely right, I have some 10 more dims and I also was trying to have some more *resultlookups multiplications within the same DATA expression and somehow skipped one Dim in KM-part and left it unrestricted, which resulted into this issue. I have split this now in several DATA expressions and looked more carefully at filters used and it worked as expected. Thank you!