Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

8fed0c28d34c4f959d4177408f838ef0.png

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

10 Replies
Not applicable
Author

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

Not applicable
Author

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 ?

Not applicable
Author

Kindly check the attached qvf file.

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

Have a good day!

Rima

Not applicable
Author

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

Not applicable
Author

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
Creator III
Creator III

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

Not applicable
Author

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

Thxs

Not applicable
Author

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.

Not applicable
Author

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;