Calculate Average with Exception Aggregation in query

[former_member191531]

Hello everyone,

we are on BW 7.5 and I have to create a query, which is showing following keyfigures based on Material and the Calendar Month:

- Quantity

- Average of the quantity based on the Material

- Deviation (abs.) of each month (Quantity - Average)

- Average of the Deviation based on the Material

So it should look like:

Material | Calmonth | Quantity | Average | Deviation | Average of Deviation

XYZ | 07.2018 | 5 | 8,00 | 3,00 | 2,67

XYZ | 08.2018 | 7 | 8,00 | 1,00 | 2,67

XYZ | 09.2018 | 12 | 8,00 | 4,00 | 2,67

I've created the "Average" as CKF1 using Quantity with Exception Aggregation "Average (All Values)" and reference characteristic "0CALMONTH", and a RKF1 with selection on CKF1 and 0CALMONTH (as Constant Selection).

Then I can calculate the Deviation as CKF2 with "ABS(Quantity - RKF1)" which is working fine.

What I'm really struggling with is the calculation of "Average of Deviation". I've tried it with all kinds of Exception Aggregation, but don't get the correct results.

Any ideas what I'm doing wrong or how this could be solved?

Many thanks,

Jürgen

[giselle_lin · Accepted solution]

Hi Juergen,

Could you please try FIX operator on the avg operand in 'Average of Deviation'?
See https://wiki.scn.sap.com/wiki/x/7gdYGQ

[former_member191531 · Accepted solution]

Dear Giselle,

I would like to reward points to you, but I don't know how?!

I also don't find any help information or how to contact you directly. Do you know how to do?

Kind regards,

Jürgen

[former_member191531]

Dear Giselle,

thank you for your additional explanation. I've tried to use the FIX in the CKF "Average of Deviation", but there's only the RKF in the formula and FIX on a single operand didn't work. Maybe there's an issue with my HANA Studio installation...

But it's also running fine with the FIX in the "Deviation", so issue is solved! 🙂

Regarding the rewarding of points I'm wondering if this possibility is still existing, as I really don't find a way how to do?! 😞 What a pity...

Have a nice day,

Jürgen

[former_member191531]

Dear Giselle,

thanks a lot for this worthful hint. Finally I've got it running 🙂

But what exactly did you mean with "avg operand"? I didn't find such a Mathematical or Data Function and using FIX on a pure keyfigure with Exception Aggregation "Average" didn't work. So I was using the FIX in the calculation of the (monthly) Deviation, then calculation the Sum and dividing through the Count. It's not the smartest way, but it's working.

Kind regards,

Jürgen

[giselle_lin]

Dear Juergen,

It is not precise to call it 'avg operand', sorry for that. I mean in 'Average of Deviation', use FIX on the formula operand that contains exception aggregation avg on the constant selection value.

I also don't know how to reward points.... But it's happy that the hint is helpful. 🙂