Cumulative figure for all values in a data element

Support - Assistance technique
1

Hello,

I am looking for help on if its possible to get a cumulative sum of all values entered over time as a DE or as an indicator.

We have a quarterly form in which values are entered that stretches across several years.

The plan is to show the breakdown of each quarterly value and the sum of all those values up to the current date.

eg Q1-2023 + Q2-2023 + Q3-2023 + Q4-2023 + Q1-2024 + Q2-2024

I’ve tried using the ‘Cumulative sum’ on the data visualiser - but this only sums the values shown in the chart.

We currently don’t have the system set up to use any aggregation imports or other processing for the data, I would like to get a sum from all the values using Date elements or indicators (not program indicators)

.yearToDate() wont be helpful as we have data from several years

2

You can achieve this by creating a program indicator and setting the following parameters:

  • In the program indicator details set the ‘Aggregation Type’ to ‘Sum’
  • in Edit expression, set the filter to the data element you want to sum. If you need to include more than one data element , simply add them using the addition operator (+).
1 Like
3

Thank you Omar.

The system we have set up does not use patient data so we have not added a program to the system which means we are unable to make use of the program indicators - only the standard indicators.

Is it possible to do this another way?

4

I found 2 possible solutions to this:

  • Using the periodOffset() and manually adding a large range of offsets
#{j08WkA0JQuu} + 
#{j08WkA0JQuu}.periodOffset(-1) +
#{j08WkA0JQuu}.periodOffset(-2) +
#{j08WkA0JQuu}.periodOffset(-3) +
#{j08WkA0JQuu}.periodOffset(-4) +
#{j08WkA0JQuu}.periodOffset(-5) +
#{j08WkA0JQuu}.periodOffset(-6) +
#{j08WkA0JQuu}.periodOffset(-7) +
#{j08WkA0JQuu}.periodOffset(-8) +
#{j08WkA0JQuu}.periodOffset(-9) +
...
...
#{j08WkA0JQuu}.periodOffset(-30) +
#{j08WkA0JQuu}.periodOffset(-31)

This depends on the computation you require - ours is simple and is quarterly so we can quite easily cover 8 years with 31 lines.

  • Changing to collect cumulative values
    while its not possible to accumulate a set of values another alternative is to collect the total (cumulative values) and then deduct the previous value from this to give the change in value over the period - think the reverse of adding up.

It would be very helpful to have a cumulative function though!