Hi, I'm using Metabase 0.32.4.
As you can see from the pictures attached, I'm searching a possibility to display a date in the chart where I didn't get a result (empty result). Any ideas?
Hi @PhilippEliasch
If you're showing a Timeseries (Settings > Axes > "X-axis scale"), then you can set the Display setting for "Replace missing values with" to Zero, which would do something like this:
Hi @flamber, Thanks for the realy fast reply.
Unfortunately I don't have this option in v0.33.4 when using a stacked chart where I show several results and where some of them return no value via SQL for that specific date. See screenshot:
@PhilippEliasch
But I specifically wrote that you need Timeseries. It's not possible for Ordinal, since it will display "as-is".
If you need Ordinal, then you need to create the SQL, so it returns all the dates.
then you need to create the SQL, so it returns all the dates.
Ok, Thanks - do you have an example for this?
@PhilippEliasch
But why can’t you use Timeseries?
It looks like you’re using Postgres, so you would generate a series - if you’re using a different database, then most are able to do similar thing, but it’s called something else.
https://stackoverflow.com/questions/11391085/getting-date-list-in-a-range-in-postgresql
I can't use timeseries, because even when it's shown correctly in the widget, it's differently shown in the dashboard. See screenshot.
But could solve the showing NULL issues, by joining into another table where a date range is available via:
SELECT * FROM
(
select *
from (select to_char(generate_series(date_trunc('day', now()) - '13 day'::interval, date_trunc('day', now()), '1 day'::interval), 'YYYY-MM-DD') as day) l12
left join (
select * from crosstab('
select creation, prio, count(*) from (
select to_char(a.modified_date,''YYYY-MM-DD'') creation, a.id, min(af.value_key) prio
from asset a, asset_feature af
--,domain d
where
a.id=af.asset_id and a.version=af.asset_version
--and a.domain=d.pathid
--and d.shortname = ''cnm''
and a.wf_target=5900
and af.feature=''tracker:external-priority''
and a.modified_date > current_date - 14
and a.type in (''ticket.faq.'',''ticket.problem.'',''ticket.project.'',''ticket.bug.'')
group by to_char(a.modified_date, ''YYYY-MM-DD''), a.id
order by 1
) as t group by creation, prio
order by 1,2'
,$$VALUES ('0'::text), ('1'::text), ('2'::text), ('3'::text) $$)
as ct ("day" text, "Critical" int, "High" int, "Medium" int, "Low" int)
)
t1 using(day)
) t2;
1 Like