0 Replies Latest reply: Aug 31, 2017 7:12 PM by Matthew McIlvride RSS

    Count instances between two dates?

    Matthew McIlvride

      I recently just got Qlik and I am trying to transfer some TSQL logic into Qlik. The SQL code I have uses a lot of sums of case statements. Currently, I am trying to convert a statement that looks something like this:

       

      SELECT Name,

                    SUM (CASE WHEN ActionDateTime >= @startDate AND ActionDateTime < @endDate THEN 1 ELSE 0 END) AS "Instances"

      FROM TableName

      GROUP BY Name

       

      Where "Name" is a string, "ActionDateTime" is a datetime, and @startDate and @endDate are dates.

       

      I am having difficulty figuring out the Qlik syntax. From reading other posts I have gathered that my expression in Qlik might look something like this:

       

      Count({<ActionDateTime, Date = {">=$(StartDate)<=$(EndDate)"}>} DISTINCT ActionDateTime)


      But this code does not work for me. Any ideas?


      Update:

      I tried running this again, and it worked this time! I must have been entering it in wrong before. However, the data coming back is different than the results when executing the query in TSQL. The values are similar, but not the same, which leads me to believe that date might be measured slightly differently between TSQL and Qlik. Could this be a problem with using datetime against date?


      Final Update:

      I tried the exact code I have written above and it worked. I must have had a syntax error that I was missing.