How to calculate a value using a calculated row in Qlik sense

[jacobiopharma]

Hi expert,

I have a project need to calculate a measure by subject and visit dates. It uses the sum of the first visit as a reference and the formula is -(The sum of following visit dates - the sum of the first visit)/the sum of the first visit. I also need to know the best measure for each subject - the lowest , and would like to know how many subject's measure is <-10% as KPI. I would like to know whether it can be done in Qlik sense. 

Please refer to one screen shot and the qvf file.

Thanks.

[brunobertels]

hi 

may be like this 

mesure =

(sum(TR1ORRES)-above(sum(TR1ORRES),rowno()-1))
/
above(sum(TR1ORRES),rowno()-1)

regards

bruno

[jacobiopharma]

Hi Bruno,

Thank you for your helps. It works but the measure changes after other column is sorted. Is there any way I can disable sorting or find another solution? 

Thanks

Jacobio

[brunobertels · Accepted solution]

Hi 

try this 

(
Sum(TR1ORRES) - aggr(nodistinct Sum({<TR1VISIT={'C1D1'}>}TR1ORRES),Subject)
)
/
aggr(nodistinct Sum({<TR1VISIT={'C1D1'}>}TR1ORRES),Subject)

[jacobiopharma]

Really appreciate your helps. Thank you again!