Create a date filter in custom query

1

Hi,

I am trying to create a custom date filter (ClickHouse db)

This is my query: please advice

SELECT `tbl`.`IDUser` AS `IDUser`, MIN(`tbl`.`Date`) AS `FirstDate`
FROM `tbl`
WHERE `FirstDate` BETWEEN {{StartDate}} AND {{EndDate}}
GROUP BY `IDUser `
ORDER By `FirstDate` ASC

I get this error: ClickHouse exception, code: 184

Ultimately, I want a working date filter in dashboard. Now I get no valid fields when I add a date filter and try to select the date field.

Thanks

2

Hi @georgetr.
Did you try to create the filter like this?

WHERE `FirstDate` BETWEEN [{{StartDate}}] AND [{{EndDate}}]

I guess you’ll need to add those brackets.

Or you can use a date field range and use one filter. i think this way is easier.

WHERE 1=1
 [[AND {{dateRangeField}}]]

Let me know if it works for you

3

Yes, this is working!

But now I have a different problem. I want to count these users daily and for this I created a nested query. I found that custom nested queries do no support filters in dashboard. Is it true? My query:

SELECT toDate(`source`.`FirstDate`) AS `FirstDate`, count() AS `count`
FROM (SELECT `tbl`.`IDUser` AS `IDUser`, MIN(`tbl`.`Date`) AS `FirstDate`
FROM `tbl`
WHERE 1=1
GROUP BY `IDUser`
ORDER By `FirstDate` ASC
LIMIT 1048576) `source` WHERE (`source`.`FirstDate` > parseDateTimeBestEffort({{StartDate}})
   AND `source`.`FirstDate` <= parseDateTimeBestEffort({{EndDate}})) GROUP BY toDate(`source`.`FirstDate`)
ORDER BY toDate(`source`.`FirstDate`) DESC

Thanks

4

Try this

with table_users as ( SELECT `tbl`.`IDUser` AS `IDUser`,
                                 MIN(`tbl`.`Date`) AS `FirstDate`
FROM `tbl`
WHERE 1=1
[[AND {{dateRangeField}}]] 
) 
select
         sum(IDUser) as IDUser,
         FirstDate
from
   table_users
group by
   FirstDate
5

It returns error from ClickHouse. Probably ClickHouse doesn’t support WITH clause.