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.
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?
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.