I added in 100 to each year in the "Baseline Supply" field. I am able to get data for the years and it comes up correctly except for 2017 where it sky rockets to a millions for some reason. I have no idea why.
Thanks for the help guys, I was able to get it work.
It also keeps the dimensions and re-calculates based on selections. It works exactly as I was hoping.
If anyone else has a look at it and has a suggestion for decreasing the computing requirement, that would be great. I applied it to my full data set and I can only have it calculate up to 7 years out. After 7 years it crashes and won't fully calculate the expression.
I have 500,000 rows of data it is going through, which isn't that much for Qlikview, but this year over year calculation through the variables multiplies with each additional year it calculates.
This did help a bit with the entrants, retirement, and immigration rates. Unfortunately I think the major problem with the computing power required is the year over year variable calculation.
I cannot attach an updated version that has the computing issue because it turns the Qlikview file into a 500MB file! The problem is each year further into the projection the variable it needs to compute becomes extremely long. It won't even write out the expression in a text box past 2018.
I am really hoping that someone can help me out here. I was very excited when it looked like it was working for the first couple years but I need to have a 10 year projection.