How to get Total in advance formulas

[Vasu_Sac]

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 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

Regards,

Vasu

[N1kh1l]

@Vasu_Sac 

In SAC All data is at every dimension level. Its just that you use # for some dimension to reduce the granularity.

Forecast ratio is not MatGroup level, it is MatLine Level. // Is it at MG level # ? Please show your second table with MG on rows along with ML.

And Sales_Forecast also should calculate at MatLine level. // Its getting calculated at ML level only.

Post the model view of this 2 data with all dimensions shown. Its difficult to help when you provide incomplete information. In your second table you should show MG also ( even if its #)

Nikhil

[N1kh1l]

@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