$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
avatar image
Former Member

Division by zero in members formulas (BPC HANA) - MDX Error

Hello all,

I am working on BPC 10.1 NW on Hana, and I have a problem with members formulas.

The problem is the division by zero, not by empty.

For example, if my formula is " 1/[NBJO] " ([NBJO] is an account in my dimension) the result in a BPC report is :

- 0 if [NBJO] is empty

- ERROR if [NBJO] is 0 (this is a mdx error in a Excel popup)

It is a problem because the account NBJO can be zero if the data is suppressed (before an optimize) or for a parent node for example.

We have contacted the SAP support and the respond is adding a function to test the NBJO value, because Hana doesn't manage the division by zero.

So, we have modified the member formula "1/NBJO" by "IIF([NBJO]=0,NULL,1/[NBJO])"

The result is ok, I have no error.

Unfortunaltely, I have others formulas with the following syntaxe :

IIF([NBJO]=0,NULL,IIF(SUM(DESCENDANTS([ENTITY].CURRENTMEMBER,20,LEAVES),1/[NBJO])=0,NULL,SUM(DESCENDANTS([ENTITY].CURRENTMEMBER,20,LEAVES),1/[NBJO])))

The objective is to have the sum of ratio and no the ratio of the sum (for a parent node)

But the tests don't work ....and we have the same error in Excel popup. The result of this member formula is good if NBJO is empty, not zero.

Have you a idea please ? Have you already see this problem ? because I have no more idea ...

Thank you in advance,

Best regards,

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 30, 2017 at 06:48 AM

    But what is the business logic to have a sum of ratio?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 30, 2017 at 09:04 AM

    Hi Vadim,

    It's a headcount project, so we have indicators about number of days.

    We have indicators with ratio of sum, but others are a sum of ratio... these indicators worked fine on BPC 10.0 (without Hana).

    But after the migration to BPC 10.1 Hana, the division by zero doesn't work

    Bests,

    Add comment
    10|10000 characters needed characters exceeded

    • Sorry, but I can't see any hierarchy in your post... To show hierarchy it's better to use screenshots!

      With script you calculate employee number on base Entity/Time/... members.

      The parents will use normal aggregation!

      P.S.

      "entity_node1 => created just for storing the result" - what for?????????????????????????