2 Replies Latest reply: Feb 21, 2017 6:44 AM by Keiran Kirkpatrick RSS

    Sum of orders in date range using qsVariable

    Keiran Kirkpatrick



      I'm trying to get the sum of orders placed in a user defined date range using qsVariable but can't quite figure it out.


      I'm importing data from SQL - here is a sample:

      2015-01-01 00:00:00123410£100
      2016-04-01 00:00:00432111£110
      2017-01-01 00:00:0043215£50
      2017-04-01 00:00:0012346£50


      I've set 2 variables as vStartYear & vEndYear and qsVariable allows the user to set them as any year ("2014","2015",etc).


      I was assuming it would be something like:  sum({$<year(ORD_DATE) = {>=$(=vStartYear) <=$(=vEndYear)}> } ORD_QTY)

      but this doesn't work.


      Does anyone know what I'm doing wrong - or is there a better way of doing this?




        • Re: Sum of orders in date range using qsVariable
          Petter Skjolden


          You can't have anything else than a field name on the left hand side of the equal sign in a Set Expression.



          You should put the search expression in your Set Expression inside double quotes "


          Here is my suggestion for you:


          2017-02-21 08_00_16-Qlik Sense Desktop.png

            • Re: Sum of orders in date range using qsVariable
              Keiran Kirkpatrick

              Hi Petter,


              Thanks for your help.


              Is it possible for me to get a copy of that app? I'm not sure what I'm doing wrong but it's not filtering out the date ranges, even if I put in the date range manually (e.g Sum(${<ORD_DATE={">=2016-01-01 <=2017-12-31"}>} ORD_QTY) is still including values outside that date range).








              Looks like I added an extra $, although it still didn't work until I loaded ORD_DATE as Date(ORD_DATE, 'DD/MM/YYYY') matching my DateFormat.


              So, Sum({<ORD_DATE={">=01/01/$(vStartYear) <=12/31/$(vEndYear)"}>} ORD_QTY) is working.


              Thank you again for the help Petter.