Kpi problem - need help to find correct formula

[#1]

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 ?

Thank you for your help,

Regards,

Tanguy

[#2]

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

[#3]

Hi,

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

Please find enclosed the Qvf.

Any other Idea to make it work ?

[#4]

Kindly check the attached qvf file.

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

Have a good day!

Rima

[#5]

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 ?

Thanks for your help,

Tanguy

[#6]

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.

[kaanerisen]

How about this

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))

[#8]

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

Thxs

[#9]

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.

[#10]

try adding this to your script (where test1 is the inline table added)

:

Test2:

Load *,

[Production]/[Area cut] as Yield

resident test1;

NoConcatenate

final:

Load *

resident Test2 order by Yield desc;

drop table test1;

drop table Test2;