Hi,
I would like to change the format of the date column from 2019-01-01 to 1 January 2019. Tried doing it using table calculation but I canโt see any formatting functions that would help me achieve it.
At this time date formatting, color formatting, and hexadecimal conversion are not supported in Looker table calculation. However, if youโre doing it on a graph, you can use %d%B%Y as the data type. You can read more about it at https://docs.looker.com/exploring-data/visualizing-query-results/time-formatting-for-charts.
If youโre showing it in a table format, that wouldnโt work. So the next best option is to modify your LookML.
html: {{ rendered_value | date: โ%d %B, %Yโ }};;
Take a read at Easy date formatting with Liquid article where its written by Brecht Vermeire, whoโs a Looker staff. ๐ Hope this helps!
Itโs not really a chart. Itโs a single value visualisation but in my opinion when single value vis type is offered you should be able to format it to a more human-readable output?
Yup! Like @nicholaswongsg said, itโs totally possible to reformat themโ I found the article written by Brecht that he mentioned, give it a read.
By default, Looker formats dates in a certain way thatโs useful for all users. However, in some occasions I would like to format my dates differently. For example, if Iโm building an explore and some dashboards for my European users, the dates should be formatted like 07/06/2018 or Jun 07, 18, not like the default 2018-06-04. Of course this is possible with SQL (e.g. using DATE_FORMAT()), but I donโt really want to write everything down in plain SQL. Liquid to the rescue The only thing we need โฆ
I donโt want to do it in the LookML and building a new explore etc. - itโs just for one specific look, so I want to do it on โfront-endโ just as display format change
Hey, I totally understand where youโre coming from! The next best question will be how can I hide the dimension while using it to create the dashboard. As Looker donโt have that feature (or at least yet), the only solution is to create a folder to hide it. (Although there have been lots of request for itโฆ)
Can you not create a calculated column in the viz and concat the stripped date parts into the string format you wish
That suggestion sounds spot on, if you only have one date field, then something like
concat(extract_days(${ascent.date_date}), " ",
if(extract_months(${ascent.date_date})=1,"January",
if(extract_months(${ascent.date_date})=2,"February",
if(extract_months(${ascent.date_date})=3,"March",
if(extract_months(${ascent.date_date})=4,"April",
if(extract_months(${ascent.date_date})=5,"May",
if(extract_months(${ascent.date_date})=6,"June",
if(extract_months(${ascent.date_date})=7,"July",
if(extract_months(${ascent.date_date})=8,"August",
if(extract_months(${ascent.date_date})=9,"September",
if(extract_months(${ascent.date_date})=10,"October",
if(extract_months(${ascent.date_date})=11,"November",
if(extract_months(${ascent.date_date})=12,"December",null)))))))))))), " "
,extract_years(${ascent.date_date}))
Thatโs assuming you only have the one date field being returned. If you use a dimension group and instead query one field for day, one field for year, and one field for month name (would get the same grouping), you wonโt need that yucky nested if() statement and could just say
concat(${table.date_day_of_month}, " ",
${table.date_month_name}, " "
,${table.date_year})
UPDATE: This method appears to no longer be working
Iโve actually come across an even simpler way.
Calculate the diff_days
between 1899-12-30 (blame spreadsheets) and your actual date.
For example:
diff_days(to_date("1899-12-30"), ${users.created_date})
Now you can use the Excel-like date formatting described here.
So for โ1 January 2019โ, use d mmmm yyyyy
in your custom value format on the table calculation.
This is amazing! Work like a charm. ๐
Thanks I will try thisโฆ
Isnโt it counter-intuitive to have to use days difference to format a date? Itโs quite simple task that a function like format_date would solve all the problems
Why wouldnโt hh:mm:ss
work here?
Looks like thatโs the way Excel & Office do it for dates only.
You can however use this calculation for readable hours:
diff_seconds(
to_date("1970-01-01 00:00:00"),
${users.created_time}) / 86400
Basically changing it from a datetime to an epoch timestamp by calculating the seconds since 1970-01-01 and changing that to days by dividing by 86400.
Then in the custom format, you can use hh:mm:ss
.
In other words I have to create to different fields, then use those functions, then create another field that would concatenate the two?
You could do it all in one larger nested table calculation as well if you only want 1 extra field in the explore to keep things tidy.
I created the second calculation called time and then used:
concat(to_string(${date}), " ", to_string(${time}))
Iโm getting 43558 17989.51638888889
Obviously itโs due to the ${date}
and ${time}
being treated as strings created from numbers because formatting is applied only later on, how do I join them then?
You can use the to_date()
calculation to turn it into a proper date type again, similar to the example above.
This doesnโt make any sense to me, sorry. Something like this:
concat(to_date(to_string(${date})), ${time})
still brings me to 17990.311168981483
as a result.
I never expected to have so much trouble with simple timestamp format ๐
Youโll need to apply it to the full concatenated string Dawid:
to_date(concat(to_string(${date}), ${time}))
That gives you the date and time concatenated, as a date type, so you can use it in date diff functions.
But to_date
will not allow me to display it in the format: 23 Jun 2019 15:25;12
, will it?
Dawid, while I can appreciate wanting this feature to be simpler, it seems that youโve got a few options at your disposal today:
Both of these would give you a solution right now thatโs fairly robust.
I tried what you suggested before but I still canโt produce the formatting Iโm after
I created Date and Time separately with formatting you specified and so far it works. Then I concatenated it to_date(concat(to_string(${date}), " ", ${time}))
and nothing displays. Tried casting both date and time to string but also showing nothing.
As far as I understand it, concat will not work here because the format is displayed after the variable uses it for table calculation. Is there any option left that will allow me to reformat the datetime 2019-01-01 10:15:23
into 1 Jan 2019 10:15:23
?
Is there anyway to do this with a custom dimension?
Table Calculations make it so Looks canโt be streamedโฆ.
Answering my own question above, I believe
diff_days(
date(1899, 12, 30),
${view_name.field_name}
)
+ entering desired custom format
should work for accomplishing as a custom dimension