The Avg calculation sum is not displayed on the chart

[Alexjjx]

Hello.

There is a calendar table with two tables dimensions and measure value Plan and Fact. It is not clear why the result is not displayed on the diagram using the formula Avg(Plan.val)+Avg(Fact.val).

A message appears: the chart is not displayed because it contains only undefined values. Same thing with formula Sum([Fact.val])/Count([Fact.val]) + Sum([Plan.val])/Count([Plan.val]).

If you leave one part of the formula, for example  Avg(Fact.val), the result is displayed.

This is not observed with the display of the formula Sum(Plan.val)+Sum(Fact.val).

Is there any explanation for this?

[robert_mika]

Does your dimensions and measure values column from the same table?

Did you check in KPi object what does the measure return?

(Sum([Fact.val])/Count([Fact.val]) )+ (Sum([Plan.val])/Count([Plan.val]))

Could you attached qvf?

[Alexjjx]

Thank you for your help, Robert

The dimensions and its measure values are contained in its own table Plan or Fact.

There is a MCalendar table that contains calculated field [MCalendar.ALOS.key]
=Date([MCalendar.PERIOD])&'-'&[MCalendar.TPAY_NAME_ALOS]&'-'&[MCalendar.CFO_CODE]&'-'&[MCalendar.ROWTYPE1]&' ALOS'
, where [MCalendar.ROWTYPE1] is a field that contains the measurement attribute - Plan or Fact

And [MCalendar.ALOS.key] is associated with keys in the Plan and Fact tables, for example =Date([FactALOS.PERIOD])&'-'&[FactALOS.TPAY]&'-'&[FactALOS.CFO_CODE]&'-Fact ALOS'
=Date([PlanALOS.PERIOD])&'-'&[PlanALOS.TPAY]&'-'&[PlanALOS.CFO_CODE]&'-Plan ALOS'

(Sum([Fact.val])/Count([Fact.val]) )+ (Sum([Plan.val])/Count([Plan.val])) in KPI object displays the result.

[rwunderlich · Accepted solution]

When using the "+" operator, if any operand is null, the expression result is null. If instead you use the RangeSum(), null operands are treated as zeros. For example:

RangeSum(Avg(Plan.val), Avg(Fact.val))

I still don't know why one of these may return null, but it could be a useful debugging exercise.

-Rob

[Alexjjx]

Hello, Rob

really strange, considering that there are no null values ​​in the data being loaded.

Your solution is fine. Following, I also tried using a check like avg(If(IsNull([FactALOS.ALOS]),0,[FactALOS.ALOS]))+avg(If(IsNull([PlanALOS.ALOS]),0,[PlanALOS.ALOS])) which not worked as well and gives the wrong result. There are enough unclear points.

Thank you very much!