How to calculate KF from Different Aggregation Levels of Data

[Vasu_Sac]

Hi 

I am calculating new KF Sales_Input from two KFs which are different aggregation levels, But my code is not populating new KF value. 

Dimensions Territory Office, Mat Line, Mat Group are multiple selections by user dynamically, hence I couldn't filter in Member selection.

I need to populate new KF (Sales_Input) at MatGroup level. Any help on this highly appreciate. 

Regards,

Vasu

[Vasu_Sac]

@N1kh1l Attached Data_Intersection.png Hope this information help you to understand my problem. I couldn't able to attach data in excel format.

[Vasu_Sac]

@N1kh1l Please help on this.

[N1kh1l]

@Vasu_Sac 

Note: Please do not post code block as image. Please provide your code as text so that community can help better.

Your code block should be structured as below. This is assuming that both Sales Forecast and Dales Output are available at same data intersection of other dimensions.

You did not specify at what Material Group the Sales Forecast is stored. If its on each Material Group and you need to add them together then below code will need adjustment as highlighted. 

Dimensions Territory Office, Mat Line, Mat Group are multiple selections by user dynamically, hence I couldn't filter in Member selection. -  This is the selection for the report but I assume your calculations should happen for all members of these dimensions. 

MEMBERSET [d/Date] = (%Date_Input%)
MEMBERSET [d/CompCode] = (%CompCode_Input%)
MEMBERSET [d/SalesGroup] = (%SalesGroup_Input%)
MEMBERSET [d/CustType] = (%CustType_Input%)
MEMBERSET [d/Measures]=("Sales_Forecast", "Sales_Output")

VARIABLEMEMBER #TotalMG of [d/MatGroup]

DATA([d/MatGroup]=#TotalMG)=RESULTLOOKUP([d/Version] = "public.Actual",[d/Measures]="Sales_Forecast",[d/MatGroup]="#") // Remove the bold part if SF is on individual MG like MG1 , MG 2 and you need to aggregate else if its on MG #  you can keep the code block as is

DATA([d/Measures]="Sales_Input")=(RESULTLOOKUP([d/Measures]="Sales_Output")/RESULTLOOKUP([d/MatGroup]=#TotalMG,[d/Version] = "public.Actual",[d/Measures]="Sales_Forecast"))*100

Br.

Nikhil

[Vasu_Sac]

@N1kh1l Thanks for your reply. Yes, Sales output, Sales forecast are available at same data intersection of other dimensions. Sales out at MatGroup level whereas Sales forecast is MatLine level. There is no direct relation Dimension MatGroup vs KF Sales Forecast. MatLine level KF Sales Forecast will stores the data. And MatLine below MatGroup dimension exist. For M1 under MG1, MG2, MG3, MG4. Yes, the Dimensions Territory Office, Mat Line, Mat Group are multiple selections in report. And calculation depends on all member values of these dimensions. I require KF Sales_Input calculation each individual Matgroup level. Sales Input Calculation: (SalesOutput / Sales_Forecast) * 100 I tried the code which you provided but getting error at second data() statement “ #TotalMG variable member cannot be used in the Resultlookupfunction”. I don’t have clue what is wrong. Please see the code and help me.

[Vasu_Sac]

MEMBERSET [d/Date] = (%Date_Input%) MEMBERSET [d/CompCode] = (%CompCode_Input%) MEMBERSET [d/SalesGroup] = (%SalesGroup_Input%) MEMBERSET [d/CustType] = (%CustType_Input%) MEMBERSET [d/Measures] = ("Sales_Forecast", "Sales_Output", "Sales_Input") VARIABLEMEMBER #TotalMG OF [d/MatGroup] DATA([d/MatGroup] = #TotalMG) = RESULTLOOKUP([d/Version] = "public.Actual", [d/Measures] = "Sales_Forecast", [d/MatGroup] = "#") DATA([d/Measures] = "Sales_Input") = (RESULTLOOKUP([d/Measures] = "Sales_Output") / RESULTLOOKUP([d/MatGroup] = #TotalMG, [d/Version] = "public.Actual", [d/Measures] = "Sales_Forecast")) * 100

[N1kh1l]

Please post the data intersection of both SalesOutput and Sales_Forecast in terms of all dimensions. Its not clear whether Material Group and Material line are diffferent dimension or one dimension with different levels. Once you provide the data intersection I will adjust the code and provide

[N1kh1l]

Assuming both Sales Forecast and Sales Output are in the same model, what is not clear from your image is what material group values are your Sales Forecast of 200 stored. In a model all records need to be assigned a value from dimension. What I expect from you is to explain dimensionality of both Sales Forecast and Sales Output as they are on different combination, without tat its impossible to help on a data action.