Year to year rollover calculation

[itchyeyeballs]

Hi all,

Not sure if this is possible, have had a look around but not found anything so thought I would ask.

We need to calculate population rollover year on year.

For example:

Year 1 population for 2016 = 100

Year 2 population for 2017 = 95

Rollover = 95/100 ( 5 dropped out)

i have set up an index column which is combination of calendar year and program year, this looks like 1701, 1702, 1703, 1801, 1802, 1803.

My data table looks like

Index - Population

1601 - 100

1602 - 95

1603 - 92

1604 - 80

1701 - 110

1702 - 108

1703 - 101

1704 - 99

etc

In our other BI tool I can just create a formula that uses the index column - 101 to reference the previous year then dynamically calculate the ratio. The only approach I can work out in VA is to pre-calculate before import which limits flexibility. Is there a way I could attack this?

Any ideas appreciated

[MikeJenista]

If you are in Designer, you can right-click on the population variable and select 'create'; one of the options should be new difference from previous period, percent difference from previous period, etc.  I believe these are dependent on having date items in the data to make the aggregation well-defined, so you might need to import the year as its own column or created a calculated date column from your Year/Program Year column.

A similar calculation is available in Explorer.

[itchyeyeballs]

Thanks Mike,

The issue with that approach is it lets me go back a calendar year but not a programme year at the same time,

For example population in year 1 2016 becomes year 2 2017

This images show how the populations progress:

[MikeJenista]

I see; I think to achieve what you want in VA you might have to separate each program population into its own column.  If you don't want to do that on the raw data, you could try making a calculated item for each cohort that checks your combination year item for the program year.  If it matches, copy the population value to the calculated item.  If it doesn't match, set to 0 or missing.

Then, you can aggregate these calculated items by year, and use the period calculations I mentioned earlier.