yhamza
1
Hello team,
I am trying to get the max and min (time) of my activity. to do so, i had to apply this function “extract_hours(${dimension}) * 3600 + extract_minutes(${dimension}) * 60 +
extract_seconds(${dimension})” in looker explorer as a personalised dimension to get it as a number.
I applied then the max and min function.
Now I am trying to convert it to hh:mm:ss but no solution is working.
I hope someone could help me.
NB: I am doing it as explorer in the personalised measures, dimensions and calculation.
Thank you in advance,
Yasser
Hmm could you convert the timestamp to unix timestamp in milliseconds instead to convert it to a number? Then it’d be easier to convert it back to a different timestamp format
Yes, I went ahead and attempted essentually what schools suggested, but used my own hardcoded reference date since unix timestamp didn’t seem to be available in that context.
I tested this out using a system_activity explore which should also exist in your instance. So if you have access to system activity you should be bale to load this (append after the instance-name base in any looker url). I didn’t finish parsing seconds more granularly than day level, but you should be able to continue the pattern to break down remainder of hours, minutes and seconds.
/explore/system__activity/history?fields=dashboard.title,minseconds_since_2000,maxseconds_since_2000&sorts=seconds_between_min_and_max+desc&limit=500&column_limit=50&vis=%7B%22show_view_names%22%3Afalse%2C%22show_row_numbers%22%3Atrue%2C%22transpose%22%3Afalse%2C%22truncate_text%22%3Atrue%2C%22hide_totals%22%3Afalse%2C%22hide_row_totals%22%3Afalse%2C%22size_to_fit%22%3Atrue%2C%22table_theme%22%3A%22white%22%2C%22limit_displayed_rows%22%3Afalse%2C%22enable_conditional_formatting%22%3Afalse%2C%22header_text_alignment%22%3A%22left%22%2C%22header_font_size%22%3A%2212%22%2C%22rows_font_size%22%3A%2212%22%2C%22conditional_formatting_include_totals%22%3Afalse%2C%22conditional_formatting_include_nulls%22%3Afalse%2C%22show_sql_query_menu_options%22%3Afalse%2C%22show_totals%22%3Atrue%2C%22show_row_totals%22%3Atrue%2C%22truncate_header%22%3Afalse%2C%22minimum_column_width%22%3A75%2C%22series_cell_visualizations%22%3A%7B%22maxdate_run_in_integer_format%22%3A%7B%22is_active%22%3Afalse%7D%7D%2C%22series_value_format%22%3A%7B%7D%2C%22hidden_fields%22%3A%5B%22seconds_between_min_and_max%22%2C%22minseconds_since_2000%22%2C%22maxseconds_since_2000%22%2C%22days_between_min_and_max%22%2C%22seconds_remainder%22%5D%2C%22type%22%3A%22looker_grid%22%2C%22defaults_version%22%3A1%2C%22hidden_pivots%22%3A%7B%7D%7D&filter_config=%7B%7D&dynamic_fields=%5B%7B%22category%22%3A%22table_calculation%22%2C%22expression%22%3A%22%24%7Bmaxseconds_since_2000%7D-%24%7Bminseconds_since_2000%7D%22%2C%22label%22%3A%22seconds+between+min+and+max%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22measure%22%2C%22table_calculation%22%3A%22seconds_between_min_and_max%22%2C%22_type_hint%22%3A%22number%22%7D%2C%7B%22category%22%3A%22table_calculation%22%2C%22expression%22%3A%22floor%28%24%7Bseconds_between_min_and_max%7D%2F%2860%2A60%2A24%29%29%22%2C%22label%22%3A%22days+between+min+and+max%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22measure%22%2C%22table_calculation%22%3A%22days_between_min_and_max%22%2C%22_type_hint%22%3A%22number%22%7D%2C%7B%22category%22%3A%22table_calculation%22%2C%22expression%22%3A%22%24%7Bseconds_between_min_and_max%7D-%28%24%7Bdays_between_min_and_max%7D%2A60%2A60%2A24%29%22%2C%22label%22%3A%22seconds+remainder%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22measure%22%2C%22table_calculation%22%3A%22seconds_remainder%22%2C%22_type_hint%22%3A%22number%22%7D%2C%7B%22category%22%3A%22table_calculation%22%2C%22expression%22%3A%22concat%28%24%7Bdays_between_min_and_max%7D%2C+%5C%22+days+%5C%22%5Cn++%2C%5C%22+and+%5C%22%5Cn++%2C+%24%7Bseconds_remainder%7D%2C+%5C%22+seconds%5C%22%5Cn++%29%22%2C%22label%22%3A%22Final+Label%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22measure%22%2C%22table_calculation%22%3A%22final_label%22%2C%22_type_hint%22%3A%22string%22%7D%2C%7B%22category%22%3A%22dimension%22%2C%22expression%22%3A%22diff_seconds%28date%282000%2C1%2C1%29%2C%24%7Bhistory.created_time%7D%29%22%2C%22label%22%3A%22seconds+since+2000%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22dimension%22%3A%22seconds_since_2000%22%2C%22_kind_hint%22%3A%22dimension%22%2C%22_type_hint%22%3A%22number%22%7D%2C%7B%22category%22%3A%22measure%22%2C%22expression%22%3Anull%2C%22label%22%3A%22max%28seconds+since+2000%29%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22based_on%22%3A%22seconds_since_2000%22%2C%22_kind_hint%22%3A%22measure%22%2C%22measure%22%3A%22maxseconds_since_2000%22%2C%22type%22%3A%22max%22%2C%22_type_hint%22%3A%22number%22%7D%2C%7B%22category%22%3A%22measure%22%2C%22expression%22%3Anull%2C%22label%22%3A%22min%28seconds+since+2000%29%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22based_on%22%3A%22seconds_since_2000%22%2C%22_kind_hint%22%3A%22measure%22%2C%22measure%22%3A%22minseconds_since_2000%22%2C%22type%22%3A%22min%22%2C%22_type_hint%22%3A%22number%22%7D%5D&origin=share-expanded
Here’s a picture of the progress using these calculations for reference:
I hope this helps!
Best Regards,
Kevin
