Knowledge Drop

How to use liquid date formatting with a dynamic timeframe field


Userlevel 1

Last tested: May 14, 2020
 

The Problem

You've seen that we can dynamically select timeframes using a parameter and dimension group and you've seen that we can do custom date formatting on individual fields using liquid, but you want to do custom formatting on the dynamic field

A Solution

Put all that date formatting into a nice conditional statement in liquid!

parameter: date_granularity {

type: string

allowed_value: { value: "Day" }

allowed_value: { value: "Week"}

allowed_value: { value: "Month" }

allowed_value: { value: "Quarter" }

allowed_value: { value: "Year" }

}



dimension: dynamic_created_date {

label_from_parameter: date_granularity

sql:

CASE

WHEN {% parameter date_granularity %} = 'Day' THEN cast(${created_date} as VARCHAR)

WHEN {% parameter date_granularity %} = 'Week' THEN ${created_week}

WHEN {% parameter date_granularity %} = 'Month' THEN ${created_month}

WHEN {% parameter date_granularity %} = 'Quarter' THEN ${created_quarter}

WHEN {% parameter date_granularity %} = 'Year' THEN cast(${created_year} as VARCHAR)

ELSE NULL

END ;;

html:

{% if date_granularity._parameter_value == "'Day'" %}

{{ rendered_value | date: "%B %d, %Y"}}

{% elsif date_granularity._parameter_value == "'Week'" %}

{{ rendered_value | date: "W%U %Y" }}

{% elsif date_granularity._parameter_value == "'Month'" %}

{{ rendered_value | append: "-01" | date: "%b %y" }}

{% elsif date_granularity._parameter_value == "'Quarter'" %}

{% elsif date_granularity._parameter_value == "'Year'" %}

{{ rendered_value }}

{% else %}

{{ rendered_value}}

{% endif %}

;;

}

 

This content is subject to limited support.                

 

 


2 replies

Currently, this does not automatically fill in missing dates with “0” measure values like regular date columns do. What is a potential solution to this? 

Hi all,

just in case someone is also facing date formatting issue with using the above html parameter, for us it worked to replace the variable ‘rendered_value’ with ‘value’ and deleting the appending.

Cheers!

Reply