cancel
Showing results for 
Search instead for 
Did you mean: 

Calculation in Multiprovider

Former Member
0 Kudos

Hi, The problem is in the Query, I need to calculate a key figure based on the other 2 key figures.

KF3 = KF1 * KF2

the only issue here is i need to find the KF1 for each material/country/fiscal period from cube 1 and KF2 for each material/country/fiscal period from ODS.

and multiply and then aggregate according to the filter & selection conditions.

can anyone let me know how to do it..

<b>its very urgent.. and any helpful answer appreciated</b>

thank you

Emil

Accepted Solutions (1)

Accepted Solutions (1)

former_member188325
Active Contributor
0 Kudos

Hi,

You can get this with restricted keyfigures and formula.

Created a restricted kf for KF1:

Restrict KF1 with material,country,period and cube1.

to restrict with cube1 ,you can use 0INFOPROV and restrict it with cube1.

0INFOPROV is available under DataPackage dimension of mltiProvider.

In the same way created one more rkf for Kf2 and do the necessary restrictions.

now create a formula/CKF to calculate RKF1*RKF2.

regards

Former Member
0 Kudos

Hi Murali, thank you for very quick response. if we create RKF on material & country & fiscal period, are we able to run a query at high level lets say EUROPE & prodh2 of material (and if i restrict on UK & material) and get the aggregated results as we require ?

thank you very much in advance !

Emil

former_member188325
Active Contributor
0 Kudos

yes..you will get aggregated value based on restrictions.

Former Member
0 Kudos

Hi, thanks again !

i created the restricted key figures with restrictions, but i am not able to include them in the structure. is it because i have already used the characteristics in free characteristics that i am using to restricting the kF ?

Best regards

Emil

former_member188325
Active Contributor
0 Kudos

Hi,

no..you should be able to do it..Including in which structure??? are you trying to include them in Characteristic structure?then its not possible.include Created restricted RKFs in KF structure..

or

right click on KF structure>new selection>drag and drop required Kf and characteristics to create RKF locally.

regards

Message was edited by: Murali

Former Member
0 Kudos

Hi Murali, it worked if i took out the material & country out of the free characteristics.

but the result is the same. i am not able to do Sum of Products(AX + BY). the result is coming as product of sums (AB) * (XY) which i dont want.

any more suggestions?

regards

Emil

Former Member
0 Kudos

Hi Emil,

you issue is in result row / column? You will have to define the correct setting for formula collision to calculate (A * X + B * Y). In KF3 properties try both "result from this formula" and "result from competing formula", one of these should do the trick.

Best regards,

Björn

Former Member
0 Kudos

Hi Bjorn, there is no formula collision. its a calculated key figure (KF3). the requirement is even though the reporting is done at a higher level than material & country and results should be sum of products of individual values per material & country & fiscal period.

Rows: KF1 & KF 2

Column: Fiscal Period

<b>New row to be added: KF3 (Sum of mathematical Products of KF1 & KF2 at the material/country level) even though we run the report at high level of hierarchy.</b>

Usually we get the mathematical product of Sums in the query..

am i able to put my point through ?

Suppose there are 3 levels of material (producy hierarchy) and 5 levels of country (geographical hierarchy)

suppose when you run the report at the level 1 for both hierarchies and want the results as sum of products at the material & country level. as that is combination that gives unique values (KF1 & KF2)

Someone please let me know if you have already faced this situation..

Thanks in advance

Emil

Message was edited by: Emil

Message was edited by: Emil J

Former Member
0 Kudos

Hi Emil,

I am sorry, I did not carefully read the thread. You are right, of course you do not have formula collision for calculated key figures. Nevertheless your problem is the same as formula collision... so you might try to define KF3 as formula based on restricted KF1 and restricted KF2 so you can provide formula collision setting.

Best regards,

Björn

former_member188325
Active Contributor
0 Kudos

Hi,

Emil in my previous post i have already mentioned to try with formula/CKF.did you try with formula?then formula collision?

regards

Answers (1)

Answers (1)

Former Member
0 Kudos

You can use infoobject 0infoprov to filter the infoprovider from where take data, expand the dimension 'data package' and you will find it.

Hope it helps.

Regards