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

    Sum of orders in date range using qsVariable

    Keiran Kirkpatrick

      Hi,

       

      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:

      ORD_DATEORD_STOCKCODEORD_QTYORD_GROSS
      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?

       

      Thanks,


      Keiran

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

          Firstly:

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

           

          Secondly:

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

               

              Thanks,

               

              Keiran

               

              *EDIT*

               

              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.