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