KF calculation - average of 3 key figures

[bkreddy]

Hi All,

I need to take the average of 3 different key figures. Below expression giving me an error "Calculation XXXX is an aggregation. It must have exactly 1 input KF."

KF4@BPL= AVG(KFl@BPL + KF2@BPL + KF3@BPL)

Any suggestions please? i tried with total/3 but its not fulfilling the requirement as there could be null values.

Regards,

BK

[piyush_parekh · Accepted solution]

Hi,

Try below :

(IF(ISNULL(KF1),0,KF1)+IF(ISNULL(KF2),0,KF2)+IF(ISNULL(KF3),0,KF3))/((IF(ISNULL(KF1),0,1)+IF(ISNULL(KF2),0,1)+IF(ISNULL(KF3),0,1))

Regards,

Piyush

[bkreddy · Accepted solution]

Hi Piyush,

Thanks again, this logic shpould work, but getting couple of errors, Function ISNULL has incorrect number of parameters.Function IF has incorrect number of parameters.

Parameters, open &close brackets are fine.

I breakdown the calculation in two parts also like C= A/b but still this syntax errors.

[piyush_parekh · Accepted solution]

Pls share configuration screenshots.

[bkreddy]

Thanks a lot, both of your inputs helped me to solve my issue.

[Irmi_Kuntze]

Finally back at my table, so I can really read what you wrote, sorry for the confusion!!!

AVG, SUM, COUNT etc are all "only" aggregation operators for one KF cross a hierarchy, not for your purpose

But Piyush actually gave the correct answer already with his second suggestion!

You can simplify it a bit like

KFCOUNT = IF(ISNULL(KF1),0,1)+IF(ISNULL(KF2),0,1)+IF(ISNULL(KF3),0,1)

RESULTKF = IF( KFCOUNT=1, 0, (KF1+KF2+KF3)) / KFCOUNT

But his formula should work as well

[Irmi_Kuntze]

my mistake, wrong operator

maybe you try

Sum( KF1, KF2, KF3 ) / Count ( KF1, KF2, KF3 )

As far as I remember, count ignores NULL, but to be save you should check model config guide

AVG is an aggregation calculation only, when you want to average on higher level the details of one KF

[bkreddy]

Hi Irmhild,

I tried the COUNT logic, and i am facing couple of errors with the Planning area check.

Here is the syntax only to calculate COUNT.

Can you help me what is wrong in this function COUNT.

Thanks in advance.

[Irmi_Kuntze]

hm, I think if you want the average of three values within the same period, you should not sum them up in the first place but use , as separator between the three: AVG( KF1 , KF2 , KF3 )

[bkreddy]

Hi Irmhild,

I tried with comma separtor, still no luck. it throws the same error message "Calculation XXXX is an aggregation. It must have exactly 1 input KF."

[piyush_parekh]

Hi,

Please try below

(IF(ISNULL(KF1),0,KF1)+IF(ISNULL(KF2),0,KF2)+IF(ISNULL(KF3),0,KF3))/3

You can utilize helper key figures with ISNULL as well.

Regards,

Piyush

[bkreddy]

Hi Piyush,

Thanks for the quick response.

I dont want to make it zero incase of null values as the average calculation changes with zeros.

KF4 = Avg(KF1,KF2,KF3)

if KF2 = zero, KF4 will be 13.3, if blank KF4 = 20.

so my requirement is to achieve 20.

Regards,