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: 
philip_radovich
Contributor III
Contributor III

KPI Calculation

I am having a problem performing a calculation for a KPI. I have a line chart that displays the average value grouped by a date range. My dimension in the line chart is AssessmentDate and I have a measure of Avg(BMI). Which gives me a break down of the average based on the date. I am trying to figure out how to calculate the difference between the latest avg value and first avg value. 

The KPI will be the latest avg value - initial avg value / inital avg value. 

Where I am having trouble though is getting the avg latest and avg inital based on the date range selected. I have tried multiple different aggr scripts and firstsortedvalue variations but I can't see to get it right. 

5 Replies
Vegar
MVP
MVP

Take a look at my attached qvf file

image.png

philip_radovich
Contributor III
Contributor III
Author

Hi, 

 

Thank you for the sample application. It does for for the most part. The only problem I am seeing now is if I filter to a specific item that does not have a value for the max assessment date the i get a null. Is is possible to use the max non null value and/or exclude null values with the set analysis? 

 

Thanks!

Phil 

dwforest
Specialist II
Specialist II

Yes, to include only non-null values, use fieldname={*} in your set expression

Vegar
MVP
MVP

As @dwforest  mention you could use the set {<fieldname = {"*"}>} in order to exclude non null values. 

In you case then fieldname might be the field in which you are making a selection, if so then you could adjust the set to this in order to union the star with your selections

 {<fieldname *= {"*"}>}

If you still have issues, please share some sample data where we can get an better understanding of your issue.

philip_radovich
Contributor III
Contributor III
Author

I am still having a problem unfortunately. While it works flawlessly when people have data for the same assessment date it's not calculating how we would like if they have different assessment dates. Basically what we are looking to do is calculate the delta per person individually and then take an average of all of those people for an Average Delta. An example of the data set is below. When I select an individual person it works fine but when both people are displayed it is calculating off the overall max(AssessmentDate)/min(AssessmentDate) vs each person.  even though the second person only has data from 9/7/15. 

qlikimage.PNG