02-13-2018 05:47 AM
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.
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
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
02-14-2018 09:47 AM - edited 02-14-2018 09:47 AM
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.
02-14-2018 11:12 AM
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:
02-14-2018 11:19 AM
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.