6 Replies Latest reply on Feb 17, 2020 7:45 AM by jbdc

    LOD Calculations (Find first value, then calculate an average rate of progress)


      I have a data set that measures program success across countries. I am using a date filter with a bar chart that allows the user to view average progress over time.

      It measures Metric Values against Target Values (in red) over time.


      Here is a look at my data

      What I'm doing is first using a Lookup calculation to identify the first metric value in one of these sets - LOOKUP (SUM ([Metric Value]), FIRST())

      The calculation seems to work. I am calling it "Baseline."


      I am then using another calculation to try and measure an average level of progress towards the Target value (Current Metric Value - Baseline/Target Value -Baseline)


      However, the results don't produce what they should.


      Below is the view of the two data (Metric) sections I first displayed (the ones with 0.4 and 0.5 values). They are being measured from a 2012-2015 data filter (but as all the Metric Values for this section don't improve, that shouldn't matter, as the results should be 0%)

      Given my calculated fields, the measurements should be displaying - (0.4 - 0.4/0.8 - 0.4) & (0.5 - 0.5/1.6 - 0.5) - which should, together, equal 0% - yet, they indicate (below) a progression rate of .0625 (6.25%)



      What am I doing wrong, and how can I make this calculation work?

      (Current Metric Value - Baseline/Target Value -Baseline)


      Message was edited by: James Bieszka