Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
philip_radovich
New 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
Partner
Partner

Re: KPI Calculation

Take a look at my attached qvf file

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
philip_radovich
New Contributor III

Re: KPI Calculation

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 

Highlighted
dwforest
Valued Contributor

Re: KPI Calculation

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

Partner
Partner

Re: KPI Calculation

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
philip_radovich
New Contributor III

Re: KPI Calculation

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