Working with to and from dates in expression

[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

[Gysbert_Wassenaar · Accepted solution]

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