Dynamic Date Formatting

Question & Answer
, ,
1

Hello!

I have a daily_date in my dataset and a reporting_period control that has Week, Month, Quarter and Year as grain selection options. This is how the formula looks (below)

image
image657×553 11.4 KB

My question is - when a user selects Year, I want to display 2023 instead of Jan 31, 2023. Is there a way to achieve that?

I saw Dynamic date hierarchy selection to do this but I don’t want to change my date datatype to string by doing this because I need to use this date for more calculations that need a date datatype. Thank you!!

ifelse(${Granularity}=‘Year’,addDateTime(-1, ‘DD’, addDateTime(1, ‘MM’, truncDate(“YYYY”,{report_day}))),

${Granularity}=‘Quarter’,truncDate(“Q”,{report_day}),

${Granularity}=‘Month’,addDateTime(-1, ‘DD’, addDateTime(1, ‘MM’, truncDate(“MM”,{report_day}))),

${Granularity}=‘Week’, addDateTime(-1, ‘DD’, addDateTime(1, ‘MM’,truncDate(“WK”,{report_day}))),

truncDate(“MM”,{report_day})

)

2

Unfortunately the only way would be to change it to a string.

The only other way you can change it is from the aggregations. Although that is static and won’t change.

image
image769×391 34.2 KB

3

Thank you Max for quick response!

4

Hi,

another solution could be, that you use the freeform mode, but this gets very tedious.

So you create for each visual every version you need. So one table with the format as YYYY, one table with QQ… and so on. Then you edit in the rule tab of the visual settings, that you want to hide the visual as default and show it, when the parameter has a specific value.

After this, you move the visuals over each other.

When the user choses “year” in the dashboard, the rule triggers and displays the table, where you used YYYY as the date format, with “month” the table with MM format is displayed and so on.

1 Like