$(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
0

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

Jun 29, 2017 at 01:26 PM

82

avatar image

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,

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Vadim Kalinin Jun 30, 2017 at 06:48 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Jorel AUGUSTIN Jun 30, 2017 at 09:04 AM
0

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,

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Please provide an example when you need to sum rates! I do not understand the case.

0

Hi Vadim,

For example, we have :

NBJO = number of workable days

DAYSWORKED = number of days worked in a entity by all employees

So, if we have 2 entities in a companie, NBJO=20 and DAYSWORKED=100 for both :

Sum of ratios :

DAYSWORKED/NBJO => 100/20+100/20=5+5=10

So we have 10 people in the companie, it's the good result

Ratio of sum :

DAYSWORKED/NBJO => 200/40=5

So we have 5 people in the companie, it's wrong...

Is it more clear for you why we have need to do the sum of ratio ?

Best's

0

Strange logic to calculate number of artificial employees... But anyway:

Use script logic to perform this calculation and store the result in the required member. No dimension formulas.

0

We thought script logic but the result must calculated for each level in the hierarchy ENTITY and TIME.

We can't create members to store the result for each level.

For exemple, if we have the entity hierarchy :

- NODE 1

- NODE 2

- Entity 1

- Entity 2

- NODE 3

- Entity 3

- Entity 4

We will have to create this :

- NODE 1

- entity_node1 => created just for storing the result

- NODE 2

- entity_node2 => created just for storing the result

- Entity 1

- Entity 2

- NODE 3 => created just for storing the result

- entity_node3

- Entity 3

- Entity 4

The dimension will be complex... and we have the same problem for TIME dimension, we can't create a member for storing the results for YEARS and QUARTERS.

It's the reason why we have used the members formulas in BPC 10.0 NW.

It's worked fine, before the migration in BPC 10.1 NW HANA

Is it exist a formula to manage the division by zero ?

I think (in spite of the SAP support response) it's not normal than the division by zero doesn't work (but by empty, yes)....

0

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?????????????????????????

0