BEx Query Percentage at Overall Result

[marcela_martinez]

Hi everybody,

I have an issue that I can't solve with a percentage calculation in a query at overall results.

Here my actual result:

As you can see at Overall Result I'm getting an X when I have to get a 0,24%.

The formula that calculates the last column is NODIM ( TOTAL HOURS)%_ANODIM ( PARCIAL HOURS), with no Aggregations and no Calculations options.

TOTAL HOURS and PARCIAL HOURS are query formulas too.

Can you help on how to obtain Overall Result Percentage value?

Thanks in advance and best regards.

[former_member492038]

What are the result rows for the TOTAL HOURS and PARCIAL HOURS you mention showing?

[cornelia_lezoch]

Hi Marce,

in your screenshot we can not see the values for TOTAL HOURS and PARCIAL HOURS

what are those values in the result line?

When you get the X it seems to me, that one value is probably 0

So either you correct those values, or do what Ashu recommends change the setting for the reult in your "Break in Work%" Key figure.

regards

Cornelia

[former_member492038]

Summation in the result rows is not a good idea. Please see my comment on Loed's answer

[ashu33]

Hi Marce,

Can you please try Calculate Result as Summation of all single Values on that formula

[former_member492038]

Summation in the result rows is not a good idea. Please see my comment on Loed's answer

[Loed]

Hi,

Please show also the values and formulas for total hours and parcial hours as these will help us understand why you get the value of "X".

Anyway, I think the solution is you apply exception aggregation to your BREAK-IN WORK with SUMMATION.

Regards,

Loed

[former_member492038]

Using summation in the result is not a good idea. The only reason why summation works in this case is because one of the percentages is zero. However, if there were 4 records with 25% individually using summation would add up to 100% in the total line, which would not be correct.

[Loed]

Hi,

I already tried this solution before for percentages having several records and it worked fine. It will only be used in the RESULT of each percentage and not by line item.

Regards,

Loed

[former_member492038]

Unless I'm missing something that's not a universally acceptable solution. (A + C) / (B + D) is not equal to (A / B) + (C / D). The former is what should be expected in this situation, but the latter is what you would get if using exception aggregation of summation.

[Loed]

Hi,

The solution I have suggested works only in the TOTAL RESULT side and not the summation of the result of the formula.

So for this formula,

FORMULA = NODIM ( TOTAL HOURS)%_ANODIM ( PARCIAL HOURS)

It will only compute based on the FINAL SUM RESULT of all line items. It will not add 0.24 + 0 as what you are saying.

Let's say I have this sample data based on the result above:

TOTAL_HOURS --- PARCIAL_HOURS --- FORMULA

0 --- 0 --- 0

24 --- 100 --- 0.24

OVERALL_RESULT --- 24 --- 100 --- 0.24

Assuming that 1st row has value like below, using EXCEPTION AGGREGATION with SUMMATION will give us this:

TOTAL_HOURS --- PARCIAL_HOURS --- FORMULA

60 --- 120 --- 0.5

24 --- 100 --- 0.24

OVERALL_RESULT --- 84 --- 220 --- 0.3818

Exception aggregation result will be 0.3818 and not 0.74 (0.5 + 0.24) as what you are saying should be the result.

...

Anyway, if ever my answer is incorrect what should be the solution? So we will know how to solve this kind of problem.

Regards,

Loed