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,
But what is the business logic to have a sum of ratio?
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,
Please provide an example when you need to sum rates! I do not understand the case.
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
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.
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)....
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?????????????????????????