5 Replies Latest reply: Apr 10, 2017 5:54 PM by omar bensalem RSS

    How to calculate a value based on a date range in Qliksense

      Hi All,


      In a typical SQL if I would do this:


                       "case when B.date between B.financialyearstartdate and Max(B.date) then sum(A.salary) end"

                        where A, B are two different tables and they are having a valid join.


      How could the same be achieved in Qliksense?


      Please suggest.




        • Re: How to calculate a value based on a date range in Qliksense
          omar bensalem

          Hi Koka,


          Please refer to this thread, I explained in details how to proceed to build time expressions in Qlik step by step.

          hope that will help:

          YTD, MTD issue


          Set expressions seems difficult at first sight, but then you'll love working with them.


          Let's suppose you have a date field (you don't have to create variables); only a date field will do the job.


          And let's suppose you've created a calendar in your script so you have Year,Month, Quarter and Week fields


          YTD: if we select 12/04/2016: YTD will return our measure from 01/01/2016 to 12/04/2016

          How we do that?


          Suppose our measure is : sum(Sales)


          1)First changes: sum({<date=,Year=,Month=,Quarter=>}Sales)


          We add these to force Qlik to not take into consideration our selection of date for example.

          Let me explain in better words, if you don't write the "date=" and select the date 12/04/2016; Qlik will filter all the data to that selected point and then return the sum(Sales) for the day 12/04/2016.

          To prohibit this, we must write the date=.


          2) Second change: sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)


          Let explain this : date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}


          We want to work from date=01/01/2016 to the selected date=12/04/2016 right?

          So we're working with the field :

          a) date={    }

          b) Now we wanna this date to be <=selected date which is max(date) ;

          max(date) is a function so it needs an "=" sign:


          when we have a '=' we add the $ (before each calculation) : $(=max(date) ) => this is 12/04/2016

          Now we add the <= so we'll have :  <=$(=max(date) )

          for the second part, we want our date to be >=01/01/2016 which is the start of the year:

          a) same approach, we use the YearStart function that returns the start of the selected year: >=$(=YearStart(Max(date)))

          Now our expression is : from : date={    }

          to : date={>=$(=YearStart(Max(date)))<=$(=Max(date))}

          Now, in a set expression, if we wanna work with the year 2016 for example which is numeric: we call it without quotes:


          If we wanna focus on a country, Tunisia for example which is a string: we call it with quotes



          In our case, we focusing on a range of dates created by an expression, so we surround it by double quotes:



          Final expression for YTD:

          sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)

          Same approach for MTD:

          sum({<date=,Year=,Month=,Quarter=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}Sales)

          if we wanna focus on the YTD-1, we wanna alter this part:


          to do so, we want to situate our self in the previous year, to do that, their is a function called addYears.

          How we use it? addYears('04/12/2017',-1) = 04/12/2016;

          So our expression will become:


          With this, if the max(Date) in 2017 is 24/03/2017 (like in your case)

          The YTD-1 will return the Sales from 01/01/2016 to 24/03/2016 .

          Hope this helps,


          • Re: How to calculate a value based on a date range in Qliksense
            Martin Baculik

            Hi Kam,


            sum( {<B.date = {">=$(=max(B.financialyearstartdate))  <=$(=max(B.Date))"}>} Salary)


            Try this expression in front end (use KPI object) and see, if it returns you relevant number. If not, come back and we can progress on that later on.




            • Re: How to calculate a value based on a date range in Qliksense

              Hi Martin and Omar,


              Thank you very much for your quick turnaround. The expression sum( {<B.date = {">=$(=max(B.financialyearstartdate))  <=$(=max(B.Date))"}>} Salary) works as expected.