I have a date column in my dataset, and I want to give the user the option to change the chart aggregation from day to month. However, there is a custom option that I call ‘decade’. This option separates the days as follows: <=10 as ‘1st decade’, <=20 as ‘2nd decade’, and the rest as ‘3rd decade’. I need this value to be accompanied by the month and year. For example, data between May 25 and 30, 2024, should be combined in my chart as ‘3rd decade - May-2024’. I need this to be a timeline where each month will have the 1st, 2nd, and 3rd decades. Is this possible?
I testing something like this, but got a error:
ifelse(
${aggregation} = ‘Day’, truncDate(‘DD’, {date}),
${aggregation} = ‘Month’, truncDate(‘MM’, {date}),
${aggregation} = ‘Decade’,
ifelse(
{dia} <= 10, ‘1º Decêndio’,
{dia} <= 20, ‘2º Decêndio’,
‘3º Decêndio’
),
truncDate(‘WK’, {date})
)
I use {dia}, because my dataset already have the day num. Thank you.