Result value issue

[Former Member]

Hi experts,

In my query I have Customer, Sales doc, Item, GI date on the row and two cal.KF and several KFs on the column. At the inital run the Result values are correct, but once I remove the char. Item from the drill down then the Result value are wrong, I have set the Calculate Result As Summation on those cal.KFs

Could you advise me how to solve this problem ?

Thanks in advance

[Former Member · Accepted solution]

Hi all,

Maybe someone can share the experience or links which can show the common rules or the tips about how to define the Rusult for those KFs and Char. during the query definition

Thanks

[former_member181964 · Accepted solution]

Hi,

Check each and every Char/KeyFigure properties and find the problem. Any Macros written in the report?.

Thanks

Reddy

[former_member184494 · Accepted solution]

Leon,

Do you have any "Before Aggregation" or Exception aggregation for the same CKF ??

[Former Member · Accepted solution]

I have checked in both query level and workbench for those FKs, there's no Before Aggregation" or Exception aggregation. all are sum.

[Former Member · Accepted solution]

There's no Macro in the report. Could you please give me more detailed guideline about how to define the properties of char. and kfs. thanks

[Former Member · Accepted solution]

The calculated Key Figure would be set at 'After Aggregation'.

After Aggregation means that the result row is calculated based on the result of the other 2 key figures.

eg

A / B / Result = A*B

5 / 2 / 10

7 / 3 / 21

12 5

Here result will be 12 * 5 = 60 instead of 10 + 21 = 31.

To remove this error, select Before Aggregation, which will give the result as 31.

But if you are using other CKF to calculate this CKF, you will not get an option of using before aggregation.

Regards,

Agrima

[Former Member · Accepted solution]

Hi Agrima,

Thanks for your input.

In my case the formula likes this:

The KF Confirmed qty = qty / number of position, here the number of position is the record numbers.

So if the item is included in the drill down then the number of position is 1. but if we remove the item from the drill down and leave Material, then for some sales orders this can be happen that multiple items in this single order contain same material , i.e. for one material can exists in several items. And each sales/item has different qty, so on the material level(in case item has been removed from drill down) the qty is sum but the number of position could be 2,3, 4 etc. then overall result of Confirmed qty = sum of qty / 2, 3, 4..

this is wrong.

For example:

SO item material qty

S1 1 m1 10

S1 2 m1 20

Then according to the formula above we get following result after itme is removed from drill down

material Confirmed qty

m1 30/2=15

The correct value should be 30.

Don't ask me remove the number of position because we need this in some cases.

So base on this situation could you please guide me how to solve this problem ?

Thanks

[Former Member · Accepted solution]

Hi,

As per my understanding, you need the following:

SO item material qty

S1 1 m1 10

S1 2 m1 20

S1 3 m2 30

then when you remove item, value should be

m1 30/1 = 30

m2 30/3 = 10

Am I right?

Did you try to make it a calculated key figure and select 'before aggregation'? Are you on BI 7.0 or BW 3.5?

Let me know.

Agrima

[Former Member]

Hi All,

I have similar issue. It worked for me when i used exception aggregation in query designer.

Is there a way to achive this in BI 7 without using Exception Aggregates ?

Thanks,

Gowri

[Former Member]

Hi Leon,

Do One thing Select The Key Figure on the right side go to properties select Aggregation -> Put Exception Aggregation as Total & Say Refrence Char. as Material or any Lowest availabe Diminsion which is tere in each Line Item.

Your problem will be resolved.

Regards,

sandeep

[Former Member]

Hi Sandeep,

In query level or worbench to do the setting ?

In query designer I could not see something regarding "Exception Aggregation"

If in RSA1-> InfoObjects to set the KF, I have tried like this:

in Aggregation Tab: Aggregation -> SUM, Exeption Aggregat. -> Summation (there' s no Total),

Agg.referen.char, here I tried to put Mat. but the system remove it out automatically and display message on th bottom say: reference characteristic is deleted (both aggregation agree).

what is this mean ?

Thanks

Leon

[Former Member]

Hi Leon,

The same u have to do in Query Designer.

Select the Key Figure on the right hand side u'll get the Option.

Regards,

sandeep

[Former Member]

Hi,

In the CKF take multiplication of Formula variables with characterstic reference of Sales order no & Sales order item. So that calculation will be done the for all the combinations of Sales order no & Sales order item.

Regards,

Anil kumar Sharma .P

[Former Member]

Hi,

Thanks for your information. I think your way should match my case.

But I don't know how to realize your idea, could you please give more detail ?

Thanks in advance.

[Former Member]

check whether there is any condition buit on key figures w.r.t to that cahrecteristic.

[Former Member]

there's no any conditions in the query

[emjay]

What is the cal. KF and can you provide an example in your case

[Former Member]

For example in my query one cal.KF is Opened Qty=Ordered qty - Billed qty

Thanks

[Former Member]

Any update ? I am waiting on line. Thanks