How to dynamically change format of date type?


#1

Hi everyone,

is it possible to change the format of a date type field depending on the selection in a button dropdown? (see screenshots)
If ‘Monthly’ is selected the values on the x-axis should have the format Y-M. Otherwise, if ‘Yearly’ is selected, they should have the format Y.
Yearly:


Monthly:
Monthly

Thanks and kind regards
Dirk


#2

Hi Dirk!
You can use variables in format option:

CASE
WHEN <%= time_format %> = ‘Monthly’ THEN ROUND_MONTH(“EKKO”.“AEDAT”)
ELSE ROUND_YEAR(“EKKO”.“AEDAT”)
END

as dimension

Hope that helps!


#3

What @c.jones said works great. If you want to get very nitty gritty about the details and don’t want the month to be shown after the year when you select yearly you can put these in your buttondropdown and set the formatting to standard:
Monthly:

CASE
WHEN MONTH(TIMESTAMP) < 10
THEN YEAR(TIMESTAMP) || ’ - 0’ || MONTH(TIMESTAMP)
WHEN MONTH(TIMESTAMP) >= 10
THEN YEAR(TIMESTAMP) || ’ - ’ || MONTH(TIMESTAMP)
ELSE NULL
END

Yearly:

YEAR(TIMESTAMP)