Dividing different units in advanced formula

[carsten4711]

Hallo,

i try to divide two measures. One is currency EUR and the other one Unit FTE.

This is just a stripped down version of my script to show the problem.

I have implemented to version to calculate the @average - line 9 and line 17

when i uncomment line 17 i get the correct result of 30(see screenshot)

in the commented version(this is my prefered one) i get 0.

It seems to have something to do with units because when i store the results in separate variables i get the expected result.

The FLOAT()s i added to the calculation in line 9 but with no effect.

Thanks for any ideas

MEMBERSET [d/Account] = ("Opex - Salaries & Wages", "FHeads_FJEmps")
MEMBERSET [d/Date] = "202411" TO "202412"
MEMBERSET [d/PD_Business_Area] = ("CSO")
MEMBERSET [d/PD_Cost_Center] = ("1000/#")
FLOAT @averagefte
FLOAT @averagesal
FLOAT @average

@average =
FLOAT(
	   	FLOAT(RESULTLOOKUP([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202411")) /
		FLOAT(RESULTLOOKUP([d/Account] = "FHeads_FJEmps", [d/Date] = "202411"))
	  )

@averagesal = RESULTLOOKUP([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202411")
@averagefte = RESULTLOOKUP([d/Account] = "FHeads_FJEmps", [d/Date] = "202411")
//@average = @averagesal / @averagefte

DATA([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202412", [d/PD_Business_Area] = "CSO") = ((RESULTLOOKUP([d/Date] = "202411", [d/Account] = "Opex - Salaries & Wages", [d/PD_Business_Area] = "CSO")) * 0) + @average

[carsten4711]

Problem solved - the problem was that not all necessary dimensions were shown in the trace table

[N1kh1l]

@carsten4711 

Using variables to store values from RESULTLOOKUP is not a good practice as these variables are additive and they are not cleared for each record. So if the code runs for 2 or months, all your variables will keep getting added. Using variables to store a constant value is a good use case.

Why not the below directly in line 19

DATA([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202412", [d/PD_Business_Area] = "CSO") = RESULTLOOKUP([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202411")/RESULTLOOKUP([d/Account] = "FHeads_FJEmps", [d/Date] = "202411")

or even better to make it scalable. YYYY.PP average will be calculated using previous months salary and FTE

MEMBERSET [d/Account] = ("Opex - Salaries & Wages", "FHeads_FJEmps")
MEMBERSET [d/Date] = "202411" TO "202412"
MEMBERSET [d/PD_Business_Area] = ("CSO")
MEMBERSET [d/PD_Cost_Center] = ("1000/#")

DATA([d/Account] = "Opex - Salaries & Wages") = RESULTLOOKUP([d/Account] = "Opex - Salaries & Wages", [d/Date] = PREVIOUS(1))/RESULTLOOKUP([d/Account] = "FHeads_FJEmps", [d/Date] = PREVIOUS(1))

In above code 202411 will be calculated using 202410 and 202412 will be calculated using 202411

Nikhil

[carsten4711]

Hallo @N1kh1l 

thanks for your comment. It's interesting to hear how variables work in SAC - i'm still quite new to SAC scripting.

i tried it with your sugested line

MEMBERSET [d/Account] = ("Opex - Salaries & Wages", "FHeads_FJEmps")
MEMBERSET [d/Date] = "202411" TO "202412"
MEMBERSET [d/PD_Business_Area] = ("CSO")
MEMBERSET [d/PD_Cost_Center] = ("1000/#")
FLOAT @averagefte
FLOAT @averagesal
FLOAT @average

DATA([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202412", [d/PD_Business_Area] = "CSO") = RESULTLOOKUP([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202411")/RESULTLOOKUP([d/Account] = "FHeads_FJEmps", [d/Date] = "202411")

With this line i don't get any changed data in the results.

With other test data my previous coding works - the other data don't have a currency - that's the reason why i've thought the units/currencies are the reason and that it works with storing the results in float variables.

About the scalable of the coding - my original coding contains previous functions - i've only removed everything to make it clearer.

Thanks so far and it would be great if you have other ideas

[umasaral]

Hi

It seems like you're working with SAP Business Planning and Consolidation (BPC) script logic to calculate averages for two measures: currency (EUR) and Unit (FTE). Based on your description, the issue arises when you try to directly divide the results within the `@average` calculation, but not when you store them in separate variables (`@averagesal` and `@averagefte`). This could indeed be related to the data types and how BPC handles unit conversions or operations involving different units.

Here’s a structured approach to address and potentially resolve the issue:

Understanding the Issue

The issue seems to be related to how BPC handles operations involving measures with different units (currency vs. unit). BPC is stringent about unit consistency in calculations, especially when performing direct arithmetic operations.

Steps to Resolve the Issue

1. Separate Calculation Approach:
- Since storing results in separate variables (`@averagesal` and `@averagefte`) gives you the expected result, consider using this approach instead of trying to directly calculate `@average` using division.

```plaintext
@averagesal = RESULTLOOKUP([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202411")
@averagefte = RESULTLOOKUP([d/Account] = "FHeads_FJEmps", [d/Date] = "202411")
@average = @averagesal / @averagefte
```

2. Unit Conversion and Data Types:
- Ensure that the data types (`EUR` for currency and `FTE` for units) are correctly handled in your BPC environment.
- If needed, use BPC functions or logic to convert units before performing calculations, although this may not be directly supported depending on your setup.

3. Check for Unit Mismatches:
- Double-check if there are any mismatches or inconsistencies in how units (`EUR` and `FTE`) are defined or handled in your BPC model or script logic.

4. Debugging and Testing:
- Test your script logic with different scenarios and sample data to understand how BPC processes the calculations.
- Use debug modes or trace functionality in BPC to identify any specific errors or warnings related to unit conversions or arithmetic operations.

Example Adjusted Script

Here’s how you might adjust your script based on the separate calculation approach:

```plaintext
MEMBERSET [d/Account] = ("Opex - Salaries & Wages", "FHeads_FJEmps")
MEMBERSET [d/Date] = "202411"
FLOAT @averagesal
FLOAT @averagefte
FLOAT @average

@averagesal = RESULTLOOKUP([d/Account] = "Opex - Salaries & Wages", [d/Date] = "202411")
@averagefte = RESULTLOOKUP([d/Account] = "FHeads_FJEmps", [d/Date] = "202411")
@average = @averagesal / @averagefte
```

Conclusion:By following these steps and considerations, you can address the issue of calculating averages for measures with different units in SAP BPC script logic. Ensure that you adhere to BPC’s handling of units and data types, and consider using separate variables for calculations if direct arithmetic operations present challenges related to unit conversions or consistency. If issues persist, consulting SAP documentation or seeking support from SAP BPC experts can provide further insights tailored to your specific environment and requirements.

[carsten4711]

ChatGPT? 😉