5 Replies Latest reply: Jan 19, 2016 4:28 PM by Oleg Troyansky RSS

    QlikSense Variable Definition Issue - pls help

      Hello,#

      I'm a novice to data visualization and am using Qlik Sense Cloud.

       

      I am trying to add two KPI's - one to show "YoY change in Revenue" in £ value and one as a percentage value. Someone told me I need to define variables to do this so I got online and tried to adapt some expressions I thought looked helpful. I am therefore using the following expressions at the moment:

       

      For £ Value:

      (Sum({$<vMaxYr = {$(=MaxYr)}>} Revenue)-Sum({$<vMinYr = {$(=MinYr) Revenue)

       

      For % value

      (Sum({$<vMaxYr = {$(=MaxYr)}>} Revenue)-Sum({$<vMinYr = {$(=MinYr) Revenue)) / Sum({$<vMinYr = {$(=Min year(2014))}>})

       

      I'm fairly clueless about the syntax and even how to define a variable in QlikSense- in laymens terms what I'm trying to show with the KPI's is:

       

      Sum(Revenue for this year) - Sum (Revenue for previous year), and

       

       

      (Sum(Revenue for this year) - Sum (Revenue for previous year)) / Sum (Revenue for previous year)

       

      Also, there will eventually be more than 2 years of data so just wondering if it's possible to create an expression that shows values based on the years selected in a filter pane, for example to see YoY growth stats for 1998 and 1999.

       

      I would be grateful for recommendations to any links / resources that might help.

       

      Best wishes,

      Braddy Longlegs

       

       

       

       

       

        • Re: QlikSense Variable Definition Issue - pls help
          Oleg Troyansky

          Hi Braddy,

           

          I can see that you are quite confused with the terminology and the syntax. I'd say some basic education, or at least a book, could help.

           

          Let me try and explain some of the basics. For the YoY comparison, we use Set Analysis (the quirky syntax enclosed in squiggly brackets { }). In the Set Analysis condition, we use filters that we apply to your data fields. For example:

           

          Field = {Values}, or Year = {2016}

           

          Sometimes, we use variables to make the values more flexible. Variables need to be enclosed in so called $-sign expansions, $( ... ). For example:

           

          Year = {$(vMaxYear)}

           

          In turn, variables can be created in the Data Load Script, using the LET statements. For example, somewhere in your Data Load Script, you could add these two statements:

           

          LET vCurrentYear = 2016;

          LET vPriorYear     = 2015;

           

          Notice that we usually name the variables beginning with a "v", to differentiate them from fields.

           

          So, with these two variables in place, your YoY formula could look like this (assuming that you already have the Year field in your data set):

           

          Sum({$<Year = {$(vCurrentYear )}>} Revenue) - Sum({$<Year= {$(vPriorYear)}>} Revenue)

           

          There is a lot more to learn and explain here, but that would make a mini-training class...

           

          cheers.

          Oleg Troyansky

          Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

          • Re: QlikSense Variable Definition Issue - pls help

            Thank you Oleg. You are a scholar and a gentleman.

            • Re: QlikSense Variable Definition Issue - pls help

              Thanks again, I spotted that and fixed it earlier and KPI's are now working great!

               

              Would this expression also work if I had more than two years of data? For example if I wanted to see YoY change for 6 and 7 years ago?

               

              Regards,

              Braddy Longlegs

                • Re: QlikSense Variable Definition Issue - pls help
                  Oleg Troyansky

                  Well, if you wanted to see YoY performance based on user selection, then variables wouldn't quite help you, right? You'd have to use another form of a $-sign expression, something like this:

                   

                  Sum({$<Year = {$(=max(Year))}>} Revenue) - Sum({$<Year= {$(=max(Year)-1)}>} Revenue)


                  Notice the equals sign "=" after the opening parenthesis. This calculation allows you to compare the max available Year with the Year before that.


                  Please use "Correct" and "Helpful" to close the issue if your question is answered :-)