Hello,
I have an SQL query that uses two "Field filter" type variables on date type fields in relative date. I add this query to a dashboard with two filters that I assign to each variable.
In this case, is it possible that these filters are linked? That is to say that if for a filter I change the value it changes the other filter.
For example: I change "Today" to "Yesterday" of filter 1, this changes the value of filter 2.
Thank you in advance.
Hi @flipflip
No, it would probably be easier for the users if it was just one filter, so consider changing that.
Hi @flamber,
Unfortunately, the 1st filter is linked to a date field of a table and the 2nd to another table.
I tried to put a Date type for the variables but then I can no longer use, in the dashboard, the "Relative date" type filters.
SELECT (
ifnull(@liv:= (
SELECT sum(`sdelivery`.`Total`)
FROM (
SELECT
`sdelivery`.`cfmflg` AS `cfmflg`,
`sdelivery`.`shidat` AS `shidat`,
(((`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) - (((`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) * `sdelivery`.`invdtaamt5`) / CASE WHEN 100.0 = 0 THEN NULL ELSE 100.0 END)) - ((((`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) - (((`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) * `sdelivery`.`invdtaamt5`) / CASE WHEN 100.0 = 0 THEN NULL ELSE 100.0 END)) * `sdelivery`.`invdtaamt1`) / CASE WHEN 100.0 = 0 THEN NULL ELSE 100.0 END)) AS `Total`,
(`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) AS `ZTMP1`,
((`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) - (((`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) * `sdelivery`.`invdtaamt5`) / CASE WHEN 100.0 = 0 THEN NULL ELSE 100.0 END)) AS `ZTMP2`,
((((`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) - (((`sdeliveryd`.`netprinot` * (`sdeliveryd`.`qty` - `sdeliveryd`.`rtnqty`)) * `sdelivery`.`invdtaamt5`) / CASE WHEN 100.0 = 0 THEN NULL ELSE 100.0 END)) * `sdelivery`.`invdtaamt1`) / CASE WHEN 100.0 = 0 THEN NULL ELSE 100.0 END) AS `ZTMP3`,
`sdeliveryd`.`netprinot` AS `sdeliveryd__netprinot`, `sdeliveryd`.`qty` AS `sdeliveryd__qty`, `sdeliveryd`.`rtnqty` AS `sdeliveryd__rtnqty`, `sdeliveryd`.`sdhnum` AS `sdeliveryd__sdhnum`
FROM `sdelivery`
INNER JOIN `sdeliveryd` `sdeliveryd` ON `sdelivery`.`sdhnum` = `sdeliveryd`.`sdhnum`
) `sdelivery`
WHERE (
`sdelivery`.`cfmflg` = 2 AND
{{dateRefLiv}}
)
), 0)
) - (
ifnull(@avoir:= (
SELECT sum(`sinvoice`.`Avoir`)
FROM (
SELECT
`sinvoice`.`invtyp`,
`sinvoice`.`accdat`,
`sinvoice`.`amtnot` AS `Avoir`,
`sinvoicev`.`sihori`
FROM `sinvoice`
INNER JOIN `sinvoicev` ON `sinvoice`.`num` = `sinvoicev`.`num`
) `sinvoice`
WHERE
(
(`sinvoice`.`invtyp` = 2 OR `sinvoice`.`invtyp` = 1) AND
(
`sinvoice`.`sihori` = 1 OR
`sinvoice`.`sihori` = 2 OR
`sinvoice`.`sihori` = 4 OR
`sinvoice`.`sihori` = 6
) AND
{{dateRefAv}}
)
), 0)
) AS "Résultat"
@flipflip The Date filter requires the use of Single Dates on the dashboard, so you would need two filters.
