Date formatting for table calculation

low_priority
done
reply
(Izzy) #4

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.

0 Likes

(Dawid) #5

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

0 Likes

(Nicholas Wong) #6

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…)

0 Likes

(Ian) #7

Can you not create a calculated column in the viz and concat the stripped date parts into the string format you wish

1 Like

(Izzy) #8

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})
2 Likes

(Brecht Vermeire) #9

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.

3 Likes

(Nicholas Wong) #10

This is amazing! Work like a charm. :smiley:

0 Likes

(Dawid) #11

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

0 Likes

(Dawid) #12

Why wouldn’t hh:mm:ss work here?

image

0 Likes

(Brecht Vermeire) #13

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.

0 Likes

(Dawid) #14

In other words I have to create to different fields, then use those functions, then create another field that would concatenate the two?

0 Likes

(Izzy) #15

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.

0 Likes

(Dawid) #16

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?

0 Likes

More formatting options for different data types
(Brecht Vermeire) #17

You can use the to_date() calculation to turn it into a proper date type again, similar to the example above.

0 Likes

(Dawid) #18

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 :smiley:

0 Likes

(Brecht Vermeire) #19

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.

0 Likes

(Dawid) #20

But to_date will not allow me to display it in the format: 23 Jun 2019 15:25;12, will it?

0 Likes

(Ben Silverstein) #21

Dawid, while I can appreciate wanting this feature to be simpler, it seems that you’ve got a few options at your disposal today:

  1. Table Calc with the diff method presented above. Very simple formula, and if you only need it for a single viz, save it as a Look perhaps so your team can recycle it easily
  2. Add a second Date Measure with the rendered_value formatting presented above.

Both of these would give you a solution right now that’s fairly robust.

0 Likes

(Dawid) #22

I tried what you suggested before but I still can’t produce the formatting I’m after

0 Likes

(Dawid) #23

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.

image

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 ?

0 Likes