How to use liquid date formatting with a dynamic timeframe field

[kevin_dunn]

Knowledge Drop

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.                

[mkumar-16452001]

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? 

[Anna_Mariscotti]

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!