topic Re: Kpi problem - need help to find correct formula in App Development

Kpi problem - need help to find correct formula

Wed, 21 Jun 2017 09:02:30 GMT

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

Re: Kpi problem - need help to find correct formula

Wed, 21 Jun 2017 09:21:03 GMT

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

Wed, 21 Jun 2017 09:51:22 GMT

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 ?

Re: Kpi problem - need help to find correct formula

Wed, 21 Jun 2017 10:51:37 GMT

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

Wed, 21 Jun 2017 12:25:55 GMT

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

Re: Kpi problem - need help to find correct formula

Wed, 21 Jun 2017 12:29:43 GMT

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

kaanerisen — Wed, 21 Jun 2017 13:15:44 GMT

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

Re: Kpi problem - need help to find correct formula

Wed, 21 Jun 2017 13:21:24 GMT

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

Thxs

Re: Kpi problem - need help to find correct formula

Wed, 21 Jun 2017 13:22:38 GMT

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.

Re: Kpi problem - need help to find correct formula

Wed, 21 Jun 2017 13:48:42 GMT

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;

Re: Kpi problem - need help to find correct formula

Wed, 21 Jun 2017 15:31:12 GMT

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