Convert date

Dear All,

I have a Date in Metabase that i want to convert to seperate entity like YEAR, Month and Day.

I have tried;

TO_CHAR(c.post_date, 'DD-MM-YYYY') AS "CUST_CREATED_DATE"
TO_CHAR(c.post_date, 'DD') AS "CUST_CREATED_DAY",
TO_CHAR(c.post_date, 'MM') AS "CUST_CREATED_MONTH",
TO_CHAR(c.post_date, 'YYYY') AS "CUST_CREATED_YEAR",

I want to achieve this so that i can add my period (YEAR, MONTH and DAY) to my Chart or Table in my dashboard
What is the best practice to that.

Hi, for me this syntax works:

date_format(created_at, “%d”) AS DAY,
date_format(created_at, “%m”) AS Month,
date_format(created_at, “%Y”) as year

Cheers, Eva

1 Like

Will try this out and get back to you.

Thank You for your swift response always Eva

Hello @EvaS,

I just noticed now that there is no Pivot Chart here on Metabase. Is there any other chat i can use to work with. Currently i have a report that contains 3 Fields.

Store Name, Month and Sales Value. A table will not help me achieve this unless i use a pivot table.

Any help please. lemme show you the result of the Date Conversion.

Hm ... I a not sure. As you split the date in several bits (text bits) metabase is probably not able to show a timeseries or any other chart based on date. And for other charts I think there are too many data in your table.

How would the ideal result look like for you? I guess Store name as x axis and date on y axis and sales value as cell value? That could be achieved by not splitting the date - but I guess I haven´t understood why you need to split the date up?

1 Like

ok. Below is what i want to achieve;

Where i have my Store name as Y Axis, Month as X axis and Sales under each respected boxes.

Do you get that now?

Ah, yes, now I get it. Then just select only the field store name, date_format(created_at, "%m") as month, sum(sales_value)

Then the setting pivot table should pop up in the chart settings.

I just created that query:

select category, date_format(donation_created_at_local, "%m") AS month, sum(amount_in_cents)
from bi_donation_values
group by category, month

(think category is your store name)

and got this pivot:
grafik

Should be what you are looking for?

2 Likes

Hello @EvaS,

This works perfectly. Here is the result.

pivot

Thank you for your help once again

1 Like

Hello @EvaS,

is there a way i can export to Excel or any other app from Metabase?

How can i adjust the font of my Dashboard, i think its too small?

Await your response. Thank You

You can export the questions - top right

I agree about the fonts - what is it about modern web apps and tiny pale grey text? As far as I can tell, the only way to change the fonts and colours is to recompile. The way Metabase is written means that the style information is scattered all around the compiled code. In the source, it is nice and neatly arranged. So far, I've managed to recompile with green replacing the blue headers. Just working my way through the rest. Would be nice to have something easier though.

2 Likes

Hi,
you can export questions, not dashboards. So when you are in question mode, there should be a small arrow at the right site of your question, allowing you to export to csv, xlsx and json.

To your second question, right now this is not possible. I think metabase is working on some stuff about formatting, have a look at github: https://github.com/metabase/metabase/issues?utf8=✓&q=is%3Aissue+is%3Aopen+font+size+

1 Like

Thank You @EvaS.

i am pretty much grateful

Thank you @AndrewMBaines

So grateful for your response.

I just tried adjusting the font size using CTRL-+ and it worked very nicely. In fact, I was very impressed with how well the different charts rendered.
Only issue I noticed was in horizontal bar charts and that was just some of the bars disappearing as ‘Others’.

Wow, could it be so easy?? I have never tried it … for Mac it works with cmd +.
Works well with time series! Thanks Andrew!

Post 40, you find all sorts of ways to adjust things like that! If I only I could prevent washed out grey text on a white background, my life would be much easier.

I did a lot of work for the RNIB (blind charity) a few years ago on making analytics available to their Visually Impaired users. It’s sad how in the last 10 years accessibility seems to have gone backwards.

2 Likes

yes I am hoping that in a new version somewhen users (or at least admins) can select the colour coding.
Just to be sure, you know that a dashboard in fullscreen mode can be turned to night mode, means black background and bright lines/fonts.

But that does not apply to dashboards in normal screen mode or for questions.

1 Like

Yes, my customer is currently using a dashboard hosted in an IFRAME in night mode. Autorefresh, parameters etc are applied from the host page.

2 Likes

This makes sense

Thanks @AndrewMBaines