cancel
Showing results for 
Search instead for 
Did you mean: 

How to get Total in advance formulas

Vasu_Sac
Explorer
0 Kudos

Hi 

I am looking advance formula to calculate Sales Forecast KF which is depends on two different data granularity in same model.

Users will enter data Sales Input at MatGroup level, , Forecast Ratio at MatLine level. The Sales Forecast KF to be calculate Total of Sales input / Forecast Ratio 

Total of Sales Input should be per Mat Line and Territory Office Level  and Forecast Ratio per Mat Line level.

Territory Office, Mat Line and Mat Group are page filters in story and users will select to enter the data.

I tried the below code but giving wrong results.

MEMBERSET [d/CompCode] = (%CCODE_Input%)
MEMBERSET [d/SalesGroup] = (%SGroup_Input%)
MEMBERSET [d/Date] = %FYMonth_Input%
VARIABLEMEMBER #TMG OF [d/MatGroup]

DATA([d/MatGroup] = #TMG ) = RESULTLOOKUP([d/Version] = "public.Actual", [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%, [d/Measures] = "Sales_Input")
DATA([d/Measures] = "Sales_Forecast") = RESULTLOOKUP([d/MatGroup] = #TMG, [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%) /
RESULTLOOKUP([d/Measures] = "Forecast_Ratio", [d/CompCode] = %CCODE_Input%, [d/SalesGroup] = %SGroup_Input%, [d/MatGroup] = "#")

Forecast_Ratio KF will store at Mat Line level so I can find by [d/MatGroup] = "#

But How to get sum of Sales Input KF, this is at MatGroup level below MatLine/ Territory Office. 

Missing some thing to understand. Please help me on advance formula.

Regards,

Vasu

 

Hi @N1kh1l 

Thanks for you reply.

Forecast ratio is not MatGroup level, it is MatLine Level. And Sales_Forecast also should calculate at MatLine level.

The code is not working as expected. Sales_Forecast Results are not calculating at MatLine Level.

I observed in trace, expected Sales_Forecast results are calculating at MatGroup level, same results should update at MatLine level.

Sales ForecastSales Forecast

Not sure how to correct the code, please help.

Regards,

Vasu

Thanks @N1kh1l 

Both are input template and users enter different level of granular data.

In second table, we won’t include MatGroup, the data enter at MatLine level. Ie, MatGroup is # populates for all entries in second table.

The first table, the data enters at MatGroup Level. You can see this MatGroup data sits below MatLine level. Ie,

The relation between MatLine and MatGroup is Each MatLine contains No.of MatGroup.

So need to calculate Sales_Forecast is  KF Sales Input  which is Sum of MatGroups per Matline from first table / KF Forecast_Ratio Forecast Ratio  which is MatLine from Second table

Screenshot.png

Regards,

Vasu

View Entire Topic
N1kh1l
Active Contributor
0 Kudos

@Vasu_Sac 

Looking at the screenshot of the table and assuming the forecast ratio is on MG # ( second table). The below should work on Actuals

MEMBERSET [d/CompCode] = (%CCODE_Input%)
MEMBERSET [d/SalesGroup] = (%SGroup_Input%)
MEMBERSET [d/Date] = %FYMonth_Input%
VARIABLEMEMBER #TMG OF [d/MatGroup]

DATA([d/MatGroup] = #TMG ) = RESULTLOOKUP( [d/Measures] = "Sales_Input")
DATA([d/Measures] = "Sales_Forecast") = RESULTLOOKUP([d/MatGroup] = #TMG,[d/Measures] = "Sales_Input" ) /
RESULTLOOKUP([d/Measures] = "Forecast_Ratio",  [d/MatGroup] = "#")

 

 

Nikhil