Date formats - week number of the year customizable?

Hi @alexander.kwauk ,

I would have a question to better understand the question, do you want to transform the data prior to create the data model by adding a cw column which represents weeks starting on Tuesday, or do you want to only create an analysis where you can have as dimension the custom cw?

Javier

Hi @javier.donos ,

it's a dimension input.

Thank you,

Alex

I see, the ROUND_WEEK,() transform each date to the first Monday in the past, we could do the same but with Tuesdays, We could transform the date to the first Tuesday in the past.

CASE

WHEN DAY_OF_WEEK(COLUM_DATE) >=2 THEN

TO_DATE( year(COLUM_DATE) || '/' || month(COLUM_DATE) || '/' || day(COLUM_DATE) - (DAY_OF_WEEK(COLUM_DATE)-2) , FORMAT ('%Y/%m/%d'))

ELSE

TO_DATE( year(COLUM_DATE) || '/' || month(COLUM_DATE) || '/' || day(COLUM_DATE) + (2-DAY_OF_WEEK(COLUM_DATE)) , FORMAT ('%Y/%m/%d'))

END

This snippet should transform every date to the closes tuesday in the past. I would suggest you save the snippet as Formula with a name like ROUND_WEEK_STARTING_TUESDAY and then you can use the same clean code as before, only changing ROUND_WEEK --> ROUND_WEEK_STARTING_TUESDAY

Hope it helps and it Works

Javier

@alexander.kwauk , I just saw a mistake the correct formula should be:

CASE

WHEN DAY_OF_WEEK(COLUM_DATE) >=2 THEN

ADD_DAYS(TO_DATE( year(COLUM_DATE) || '/' || month(COLUM_DATE) || '/' || day(COLUM_DATE) , FORMAT ('%Y/%m/%d')), - (DAY_OF_WEEK(COLUM_DATE)-2))

ELSE

ADD_DAYS(TO_DATE( year(COLUM_DATE) || '/' || month(COLUM_DATE) || '/' || day(COLUM_DATE) , FORMAT ('%Y/%m/%d')),(2-DAY_OF_WEEK(COLUM_DATE)))

END

That should work, I think..

hI Alexander

Javier's solution should work if I understood correctly.

If you want to change the week itself, Celonis allows you to create your own Week calendar by using REMAP_TIMESTAMPS, see example below

REMAP_TIMESTAMPS (

table.EVENTTIME ,

MINUTES ,

WEEKDAY_CALENDAR (

TUESDAY 08:00 - 16:00

WEDNESDAY 08:00 - 16:00

THURSDAY 08:00 - 16:00

FRIDAY 08:00 - 12:00

)

)