Planning On Totals Not Working for Quantity Converted UoM?

[Former Member]

Hello Experts,

We have an issue in which we are getting an error when we try to plan on totals rows that include a Key Figure which has quantity conversion to a new Unit of Measure.  The situation is as follows:

We have built a planning template (input-ready query) on a direct-update DSO to do price and rate planning.  Most of the Key Figures are set to no aggregation (NO2), with "Disaggregation Copy" set as the planning setting for planning on totals.  In order to enable planning across different converted currencies and units of measure, we have included additional key figures to store the conversion factors since you can not plan on a converted KF.  We then use calculated KFs and inverse formulas to allow users to plan in the desired currency and UoM, even though the actual plan data is being saved back in the original currency and UoM.  For the most part, everything is working perfectly as expected.

However, we have an issue specifically with the UoM conversion factor on Totals rows.  For certain material-specific UoM conversions, the conversion factors differ slightly per material, resulting in an 'X' displayed in the Totals row.  When we try to plan on the totals, it gives the message:  Element '{KF Name}' has internal value '0 (*)'. 

We have tried setting the local calculation behavior to "Average of Detailed Values That Are Not Zero, Null", which displays the correct value in the totals row but does not resolve the inverse calculation error.

Is there any setting or approach that can be used to push the totals value calculation to the planning engine so it will pick up the average (or desired results calculation) for use in the inverse formula?

Thanks in advance for any helpful suggestions!

Tony

[TammyPowlas · Accepted solution]

Hi Tony - it would help if you could share your version of Analysis Office, BW, etc.

Would this SAP Note apply? https://launchpad.support.sap.com/#/notes/2237011/E

[Former Member · Accepted solution]

Hi Tammy!

Thank you for pointing me to the note.  It looks like we are impacted by that issue, although the note seems to be related to the display of currencies / units rather than how the internal value is stored for total lines.  We will apply the correction and let you know if that works.

As for our versions, we are on:

BW 7.4 SP13

Analysis Office 2.1

HANA Rev. 102.06

[gregor_dieckmann · Accepted solution]

Hi Tony,

some calculations are done in AO and some are done on the server. All inverse formula calculations are done on the server and the operands are taken from the result set that was send to AO. Then AO might calculate some cells in the result set, the server knows nothing about it.

Are you using the local calculation setting in the query?

You might check if the effect vanishes when you use formula exception aggregation.

Regards,

Gregor

[Former Member · Accepted solution]

Hi Gregor,

Thank you for the reply and explanation of how AO handles some of the calculations.

We are using the local calculation setting in the query to set it to "Calculate Results As..." --> "Average of Detailed Values That Are Not Null".  If we don't turn that setting on, it just shows 'X' in the total row for the UoM field being used in the calculation.  Regardless of the setting turned on or off, the issue remains the same.

I have tried turning on formula exception aggregation on the inverse formulas as you suggested, but it did not resolve the issue.  Were you referring to the inverse formulas or the original CKFs for exception aggregation?

Thanks,

Tony

[gregor_dieckmann · Accepted solution]

Hi Tony,

the original value '*' indicates an exceptional value, maybe because of inhomogeneous units. The average calculation you have used ignores zero values, NULL and ERROR. This why you get a real number and not '*'. Since average calculation is done in AO the server only has '*' for the aggregated value.

You have to calculated the average on the server. This means you should try to use formula exception aggregation for the operand of the input-ready formula where you by now use local calculation.

Regards,

Gregor

[Former Member · Accepted solution]

Hello Gregor,

Thank you very much for the helpful answer.  Apologies that my response has been so delayed, but we have finally had a chance to interpret and test out your suggestion.

Adjusting the operand of the input-ready formula to use exception aggregation rather than local result calculation does seem to be bringing in the correct value on the results line.  I can tell that it appears the values are being calculated at the server level rather than the AO level. 

However, by setting the exception aggregation, it makes the results lines not input ready.  We are unable to enter plan values in the totals rows once switching on exception aggregation.

Is there any additional setting or query design approach where we can leverage this functionality while still keeping planning enabled on the results rows?

Thanks,

Tony

[gregor_dieckmann · Accepted solution]

Hi Tony,

my suggestion was with respect to element 'Source UOM to Req': this element also was not input-ready.

In your latest screen shot other elements are also not input-ready, but from the screen shot it is not possible to tell you the reason. Here are some rules about input-ready query 'elements':

1. The element is a base key figure or restricted key figure: without disaggregation is only input-ready if all characteristics in the aggregation level are uniquely determined (via drill-down, single value in fix or dynamic filter in restricted key figure); if disaggregation is used, still unit characteristics and 0INFOPROV have to be uniquely determined.
2. The element is a formula: a formula can only be input-ready if at least one operand is input-ready. If the formula uses exception aggregation all characteristics used in the exception aggregation have to be uniquely determined.

Remark:

Navigation attributes are treated as normal characteristics if there exist a non-trivial restriction in fix or dynamic filter: non-trivial here is defined as all filters except the empty or single value restriction.

Regards,

Gregor

[Former Member · Accepted solution]

Hi Gregor,

Thank you very much for your response and clarification!  Your suggestion resolved the issue and I was able to get it working as desired.

Instead of setting up exception aggregation on my BEx formulas that utilized the operand, I set it up directly on the InfoObject for "Source UOM to Req" within BW.  Once that setting was in place, I removed all exception aggregation within BEx and it worked as desired.

Thanks again for your help and swift replies on this issue!

Best Regards,

Tony