## Year to year rollover calculation

Highlighted
Frequent Contributor
Posts: 130

# Year to year rollover calculation

Hi all,

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

SAS Employee
Posts: 12

## Re: Year to year rollover calculation

[ Edited ]

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.

Frequent Contributor
Posts: 130

## Re: Year to year rollover calculation

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:

SAS Employee
Posts: 12