1 Reply Latest reply: Feb 6, 2017 2:08 PM by Gysbert Wassenaar RSS

    Working with to and from dates in expression

    Michael Vaisgaard

      Hi,

       

      I need to show only data from a specific date range in my graph.

       

      I.e.

       

      SELECT customer, count(1)

      FROM some_table

      WHERE start_date <= '01-02-2016'

      AND end_date >= '01-02-2016'

      GROUP BY customer

       

      I haven't been able to find an example anywhere to do this in expression on Sense.

       

      Any ideas?

       

      Thanks

        • Re: Working with to and from dates in expression
          Gysbert Wassenaar

          You can use the SQL statement in the script as-is. Or if you want a chart expression you can try a set analysis expression like count({<start_date={"<=01-02-2016"}, end_date={">=01-02-2016"}>}1). That may or may not work depending on the date types of the original source fields and any processing and/or formatting applied during the load. It could be you need something like count({<start_date={"<=$(=Date(MakeDate(2016,2,1),'DD-MM-YYYY'))"}, end_date={">=$(=Date(MakeDate(2016,2,1),'DD-MM-YYYY'))"}>}1).