topic Re: KPI Calculation in App Development

KPI Calculation

philip_radovich — Mon, 15 Jul 2019 16:28:46 GMT

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.

Re: KPI Calculation

Vegar — Mon, 15 Jul 2019 17:15:46 GMT

Take a look at my attached qvf file

Re: KPI Calculation

philip_radovich — Mon, 15 Jul 2019 19:25:40 GMT

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

Re: KPI Calculation

dwforest — Mon, 15 Jul 2019 19:31:08 GMT

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

Re: KPI Calculation

Vegar — Tue, 16 Jul 2019 08:58:54 GMT

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.

Re: KPI Calculation

philip_radovich — Wed, 17 Jul 2019 14:21:48 GMT

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.