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

Kpi problem - need help to find correct formula

Tanguy de Becdelievre Jun 21, 2017 5:02 AM

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

Rima Hajou Jun 21, 2017 5:21 AM (in response to Tanguy de Becdelievre)

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
Like Show 0 Likes (0)

Actions
- Re: Kpi problem - need help to find correct formula
  
  Tanguy de Becdelievre Jun 21, 2017 5:51 AM (in response to Rima Hajou)
  
  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 ?
  
  Kpi problem.qvf 176.0 K
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Kpi problem - need help to find correct formula
    
    Rima Hajou Jun 21, 2017 6:51 AM (in response to Tanguy de Becdelievre)
    
    Kindly check the attached qvf file.
    Please let me know if it works and mark my answer as correct .
    Have a good day!
    
    Rima
    
    Kpi problem.qvf 176.0 K
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Kpi problem - need help to find correct formula
      
      Tanguy de Becdelievre Jun 21, 2017 8:25 AM (in response to Rima Hajou)
      
      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
      
      Like Show 0 Likes (0)
      
      Actions
Re: Kpi problem - need help to find correct formula

Rima Hajou Jun 21, 2017 8:32 AM (in response to Tanguy de Becdelievre)

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.
Like Show 0 Likes (0)

Actions
- Re: Kpi problem - need help to find correct formula
  
  Tanguy de Becdelievre Jun 21, 2017 9:21 AM (in response to Rima Hajou)
  
  Could you explain to me how to sort it directly from the script ?
  
  Thxs
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Kpi problem - need help to find correct formula
    
    Rima Hajou Jun 21, 2017 9:48 AM (in response to Tanguy de Becdelievre)
    
    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;
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Kpi problem - need help to find correct formula
      
      Tanguy de Becdelievre Jun 21, 2017 11:31 AM (in response to Rima Hajou)
      
      I does not work, when I add some data, there is no change
      
      Like Show 0 Likes (0)
      
      Actions
Re: Kpi problem - need help to find correct formula

kaan erisen Jun 21, 2017 9:15 AM (in response to Tanguy de Becdelievre)

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))
Like Show 0 Likes (0)

Actions
- Re: Kpi problem - need help to find correct formula
  
  Tanguy de Becdelievre Jun 21, 2017 9:22 AM (in response to kaan erisen)
  
  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.
  
  Like Show 0 Likes (0)
  
  Actions

Go to original post