Lesson 06 - Accumulating Measures, Year-To-Date

Lesson 6-1 Accumulating Data Values Over Time

Lesson 6-1 Accumulating Data Values Over Time

Goal - The goal of this lesson is to create a report that has a data values from a Performance Measure accumulated over time. For this example we will be taking monthly data points and combining them into annual data for analysis. This same process can be repeated to combine monthly into quarterly or quarterly into annual and much more.

  1. Click Reports on the main menu
  2. Click on the New Report menu item
  3. On the "Data Sources" tab. Select the checkbox next to PerfMeasures, PerfMeasureValues, and TimePeriods.
  4. Click on the "Fields" tab.
  5. Under the "Fields" tab - Click on the drop down arrow and choose "Title" from the PerfMeasure table. Set the description as "Name".
  6. Choose "StartDate" from the TimePeriods table. Set the description as "Accumulated Year".
  7. Choose "ActualValue" from the PerfMeasureValues table. Set the description as "Name".
  8. Let's target the report for a specific Performance Measure. Click on the Filters tab and add a filter Field for PerfMeasure ID equals to the ID of your measure. (optionally - you can also use other filters for name etc. but ID is the most reliable). For the sake of this exercise you will want to pick a Performance Measure with a monthly frequency.
  9. Click the "Preview" tab. You should see a basic report with Actual Values, Start Dates, and the Title of the Performance Measure for those values (if Actual Values exist). Now let's get the accumulation going.
  10. Click on the Fields tab
  11. On the StartDate row - select the"Group(Year)" function
    1. This setting will also let you accumulate the data into quarters, months, years etc. By simply changing this function you can decide what timeframe you want to look at for the selected Performance Measure.
  12. On the ActualValue row - select the"Sum" function
    1. Selecting Sum here will add up all the values found inside the year. Alternatively if you are working with a percentage you will likely want to use the "Average" function instead.
  13. Click "Preview" again and you will see that now instead of monthly data points the data has now been combined into years.
  14. Click on the "Save as" tool bar button, name your report, for example "Lesson 6-1", and enter the category you want the report to be shown under, for example "Training", click ok. The custom report is saved.