Updating Dimension Attributes in a model

[PS_1978]

Hello Team,

I request some help with a requirement to incorporate YTD Gross values from external files into tax calculations. Currently, YTD values are derived from monthly wages in the system, but we now need to use the YTD from the file for the last month of each quarter.

1. Flow:

   • For March 2025, overwrite the YTD value with the file’s YTD.

   • For April 2025, calculate YTD as (March’s file YTD + April’s SAC-calculated wage).

2. Approach Followed:

   • Step 1: Load the file into the “Actual” version, mapping Employee ID, Period, and YTD to dimensions/measures. Default values (e.g., 1900-01-01, #) are used for missing attributes like dates, company code, shift, etc.

   • Step 2: Use a data action to copy from “Actual” to “Working Forecast,” assigning dimension values from master data.

   • Step 3: Shift data is pulled from a different model using LINK (since it’s absent in master data).

3. Issue: Company code is not in master data but exists in the same model under a different data source. Attempts to use RESULTLOOKUP failed.

Code Snippet:

// Define Membersets

MEMBERSET [d/Measures] = "SALARY"

MEMBERSET [d/GL_ACCOUNT] = ("EMP_SALARY")

MEMBERSET [d/MEASURE_TYPE] = "M00"

MEMBERSET [d/TIME] = %CutOffMonth%

// 1. Copy value from Actual/PROLL_YTD_GROSS to CALC-REG/SS_YTD5

DATA(

// Target Context

[d/DATASRC] = "CALC-REG", [d/GL_ACCOUNT] = "SS_YTD5", [d/TIME] = PREVIOUS(1, "MONTH", [d/TIME]),

// Populate Dimensions from Master Data

[d/START_DATE] = [d/EMPLOYEE].[p/ST_DATE_FORMATTED], [d/COSTCENTER] = [d/EMPLOYEE].[p/COSTCENTER],

[d/EMPSTAT] = [d/EMPLOYEE].[p/POS_STAT],

// Explicit Empty Dimensions

[d/END_DATE] = "", [d/TERM_DATE] = ""

) =

ROUND(

  RESULTLOOKUP(

   [d/VERSION] = "public.Actual", [d/DATASRC] = "PROLL_YTD_GROSS",

   [d/TIME] = PREVIOUS(1, "MONTH", [d/TIME]), [d/SHIFT] = "#"

  ), 2)

* ((

LINK(

[SHIFT_MAPPING], [d/Version] = "public.Update Shift", [d/Date] = "202301", [d/EMP_CLASS] = "#",

[d/EMP_TYPE] = "#", [d/SHIFT_CODE] = "#", [d/WORK_SCHEDULE] = "#"

) * 0

   ) + 1)

Any inputs would be greatly helpful.

Thanks in Advance,

Phani

[William_Yu]

Still not clear on what are you trying to achieve and what real difficulties you are facing. 

1. By saying 'YTD values are derived from monthly wages', are you calculating YTD from periodic value via calculated measure? 
2. I guess not only March's YTD is from file, but also previous months of 2025, right? 
3. Why you need separate model for this? But not just a different version and measure in same model? 

  To me, a simlper way is to just to load periodic value from file for month Jan, Feb and March into the working forecast version, then restricted measure can calcualte YTD on the fly for all your periods on the fly. 

[PS_1978]

Thanks @William_Yu for your response.

Background:
We use annual salaries from SF (loaded as Actuals) and split them into monthly forecasts using SAC Data Actions to calculate future YTD values.

New Requirement:

• Use external YTD values for the last month of each quarter (e.g., March 2025).
• Example: For April 2025, YTD = March’s YTD (from file) + April’s SAC-calculated salary.

Issue:

• The Company Code is missing for the March 2025 YTD record (loaded from the file), causing reporting issues in the story.
• Without Company Code, the story cannot display YTD values correctly.

Data Loaded from file

ID	Period	YTD	Data Src	Cost Center	Comp Code
1234	202503	45,000	PROLL_YTD	#	#

 After Copying to forecast

ID	Period	YTD	Data Src	Cost Center	Comp Code
1234	202503	45,000	WK-FCST	CC1	#

 Ask:
Need help dynamically updating the Company Code for these records (tried RESULTLOOKUP without success).

I hope I was able to better explain.

Thanks,

PS

[William_Yu]

Sorry, still not fully understand your problem here. But I assume anyway you need maintain the relation between the empolyee ID and company code in master data like you did for cost center.

[PS_1978]

Sorry, I couldn’t explain it clearly. However, I have maintained the company code in the dimension and used it in resultlookups. I didn't want to follow this approach and wanted to use it as a last option.

Could you help me understand how to use a “restricted measure” to calculate year-to-date (YTD) values? Note that, I use these YTD values to calculate taxes.

Thanks,
PS

[William_Yu]

To create YTD with restricted measure: Restrict on time dimenion -> Navigation -> Year to Date