Variable help

[#1]

Hi everyone,

I am having some trouble with variables. 

Background:

I have Labor Supply data that I am looking to project based on three variables that can be manipulated for each year.

(variables = Retirement, Educational graduates, and Immigration)

I am attempting to use variables to calculate the next year based off of the previous year.

I have attached my test document where I am trying to get the variable definitions and expressions to work.

Any help would be greatly appreciated!

Thanks,

Kosta

[#2]

Not sure I follow 100%, but try using dollar sign expansions in your variable definitions. Something like this:

$(Supply2015) - ($(Supply2015)*$(R2016)) - ($(Supply2015)*$(I2016)) + ($(Supply2015)*$(E2016))

[#3]

This got me a step closer.

Now each year is calculating the correct value.  Although now I cannot get it to show the year over year data in the chart.

Ideally I would have a bar in the chart for 2015, 2016, 2017 and 2018.

I am attaching the updated document.

[oknotsen]

Did some fooling around in a Text Object with the definition of your Supply2016 definition.

I updated your variable to this and it worked:

$(Supply2015) - ($(Supply2015)*R2016) - ($(Supply2015)*I2016) + ($(Supply2015)*E2016)

Edit: And apparently I was a bit slow in replying.

[#5]

With your current data model, I think you would need to make each year a separate expression.

An alternative could be to include the R, I, and E values in the model itself, rather than as variables.

[#6]

The only issue with putting the variable in the model itself is I need to be able to adjust the variables to alter the projection.

[#7]

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.

I am getting closer and closer though!

[#8]

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.

Regards,

Kosta

[#9]

I would make the yearly "rates" (not sure if that is actually what they are) that you are currently storing as variables part of the data model as input fields.

This way you could have a true relationship with your year dimension, but still allow the user to change the values for what-if scenarios.

[#10]

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.

Thanks for the help thus far!