9 Replies Latest reply: Apr 14, 2015 11:54 AM by kosta christopher RSS

    Variable help

    kosta christopher

      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

        • Re: Variable help

          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))

          • Re: Variable help
            Onno van Knotsenburg

            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.

            • Re: Variable help
              kosta christopher

              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!

              • Re: Variable help
                kosta christopher

                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

                  • Re: Variable help

                    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.

                      • Re: Variable help
                        kosta christopher

                        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!