10 Replies Latest reply: Jun 21, 2017 11:31 AM by Tanguy de Becdelievre

# Kpi problem - need help to find correct formula

Hi Everybody,

I am facing some problem getting the KPI I want.I need to find the correct formula to get the following KPI:

What is the area with an average yield above 90 ?

Here is my data sample:

By sorting with the highest yield on the top in a table, and then calculating the cumul yield and area, I find easily the correct answer, which is in this case 124.6.

Does anyone know how to get this value directly in a KPI ?

Regards,

Tanguy

• ###### Re: Kpi problem - need help to find correct formula

Hello,

can you try this?

firstsortedvalue( CumulArea, aggr( sum({<YieldCumul>{'90'}>}YieldCumul),FIELDNO))

let me know if it doesn't work and please attach the qvf file

Rima

• ###### Re: Kpi problem - need help to find correct formula

Hi,

I cannot make it work as Cumul Area and Cumul Yield are Rangesumabovefunctions calculated in this table.

Any other Idea to make it work ?

• ###### Re: Kpi problem - need help to find correct formula

Kindly check the attached qvf file.

Please let me know if it works and mark my answer as correct .

Have a good day!

Rima

• ###### Re: Kpi problem - need help to find correct formula

Hi,

This works but when I load new data (at the bottom), the formula does not update even if it should (I tried with a field at 100 yield )

This is probably due to the fact that when I loaded the script, the data loaded was in the following order: Fields with the highest yields on top.

Any idea how to fix this ?

Tanguy

• ###### Re: Kpi problem - need help to find correct formula

you can sort the data by Yield (ascending) from the script and then try to reload it.

I think this will solve the problem. let me know if it doesn't .

EDIT:

You can add the Yield field as a new field in the script and then sort it from the script.

• ###### Re: Kpi problem - need help to find correct formula

Could you explain to me how to sort it directly from the script ?

Thxs

• ###### Re: Kpi problem - need help to find correct formula

:

Test2:

[Production]/[Area cut] as Yield

resident test1;

NoConcatenate

final:

resident Test2 order by Yield desc;

drop table test1;

drop table Test2;

• ###### Re: Kpi problem - need help to find correct formula

I does not work, when I add some data, there is no change

• ###### Re: Kpi problem - need help to find correct formula

max({<FIELDNO={"=RangeSum(Above(sum(Production),0,RowNo()))/RangeSum(Above(sum([[Area cut]),0,RowNo()))>90"}>}aggr(RangeSum(Above(sum([[Area cut]),0,RowNo())),FIELDNO))

• ###### Re: Kpi problem - need help to find correct formula

hi this works to but same problem as below,

when I load new data (at the bottom), the formula does not update even if it should (I tried with a field at 100 yield )

This is probably due to the fact that when I loaded the script, the data loaded was in the following order: Fields with the highest yields on top.