Referencing quarter with parameter after casting quarter into a string

[mandy_chan]

Knowledge Drop

Last tested: Aug 31, 2020
 

Users typically like to follow this article where they are required to cast the CASE WHEN into the same datatype due to their SQL dialect.

When we cast quarter into a type varchar, we'll get 2016-01 returned instead of 2016-Q1. How do we get around this problem?

Solution

To do this, we can use the HTML parameter and leverage rendered_value to show the value as is on the front-end. For example:

dimension: dynamic_receive_timeframe {

type: string

sql:

CASE

WHEN {% parameter dynamic_receive_time %} = 'Quarter' THEN CAST(${order_items.returned_quarter} as char)

END ;;

html: {% if dynamic_receive_time._parameter_value == "'Quarter'" %}

{{ returned_quarter._rendered_value }}

{% endif %};;

}

This content is subject to limited support.