Query on calculation context

[jino_jose]

Hi Team,

In my inventory cube the stock values are stored at plant level  and the stock quantities are stored at plant and storage location or at plant level. The column P1/Not assigned indicates that there is no storage location available for that record.

The data set will look like below

plant	storage location	Material	stock value	stock qty
P1	P1/Not assigned	Mat1	1257.34	0
P1	P1/Not assigned	Mat2	7361.3	1
P1	P1/Not assigned	Mat3	13.48	0
P1	P1/Not assigned	Mat4	377.9	0
P1	P1/Not assigned	Mat5	273.5	0
P1	P1/C01	Mat3	0	30
P1	P1/C01	Mat5	0	0
P1	P1/C03	Mat1	0	1
P1	P1/C03	Mat4	0	41
P1	P1/C07	Mat5	0	6

The expected output  looking at webi level is

Plant	storage location	Total stock value	total stock qty
P1	P1/not assigned	7361.3	1
P1	P1/C01	13.48	30
P1	P1/C03	1635.24	42
P1	P1/C07	273.5	6

To achieve above result I tried to calculate the unit price for each material then multiply with the quantity

The formula I  used to calculate unit price is  (stock value in (plant,material)/ stock qty in(plant,material))

Total stock value = stock qty * unit price , When used this formula on the block I am getting the total stock value as 9286.53 which is the total stock value for the entire plant. Where as I am trying to calculate the total value for each plant and storage location level.

Can anybody give some input on the calculation context can be used to get the above result.

Thanks,

Jo.

[Former Member]

Can you explain how you come to the numbers in the expected output.. to me the expected output does not looks correct..

[jino_jose]

Hi Mishra,

I think the storage location P1/Not assigned might have confused you. The webi is created on top of a bex query and storage location is compound attribute with plant so if there is no value the bex query returns the field as not assigned.

Consider the P1/C01, the expected output for  P1/C01  the stock value is 13.48  and qty is 30. P1/C01 holds to material MAT3 and MAT5 with quantity 30 and 0 respectively , so the total stock will be 0+30 = 30. The stock value for MAT3 and MAT5 has to be take from P1/not assigned.  the value for MAT3 is 13.48 and the value for MAT5 is 273.5  The total value will be 1*13.5 + 0*273.5 = 13.5

Hope it clarifies

Thanks,

Jo.

[Former Member]

I will suggest to get the data for each part into same record to make the calculation easier..

You will have to build two queries.. one with plant , storage loc , material and stock value in one query..

And part and stock quantity into another...

Once these two queries are setup just merge the query on material dimension which will give you the values in the same record and then you can have the formulas which will work for you..