BookmarkSubscribeRSS Feed
itchyeyeballs
Pyrite | Level 9

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

3 REPLIES 3
MikeJenista
SAS Employee

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
Pyrite | Level 9

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:

 

Capture.GIF

MikeJenista
SAS Employee

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1506 views
  • 0 likes
  • 2 in conversation