Derive weeknumber from date in CDS view or Query

[Former Member]

Hi,

We'd like to do some of our reports by Week based on the date of the transactions, I can't find a formula to add a calculated field for the week number. Is there a way to do this?

kind regards,

Jeroen Zijderveld

[former_member511243 · Accepted solution]

Hello Jeroen,

If you are looking to compute the week number in the year, you can simply compute the number of days between 1st January and your current date, divide by 7 and add 1. Basically: nr_days_between(1st Jan, your date)/7 + 1.

Please use these date formulas available for CDS Views: https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abencds_f1_date_functions.htm

[former_member155380 · Accepted solution]

Hi

This is not accurate.

If the days in first week plus the days in last week is less than 7 days, Say first day is July 1, last day is July 31, there will be 6 weeks, but your formula is 5 weeks.

[vincenzocappelluti]

Try to join this table scal_tt_date and get 'yearweek' field.

Bye.

[former_member133963]

I've had this issue resolved previously with Jeroen's code. However, now in the new year the CDS view/query I have created returns dates up till 04.01.2021 (monday) as week 1, and from 05.01.2021 (tuesday) as week 2. Any idea's what could be the reason?

Kind regards

[Former Member]

Dear All,

Final result for week number:

division( DATS_DAYS_BETWEEN(cast( concat( substring( I_TimeSheetRecord.TimeSheetDate, 1, 4 ), '0101' ) as ABAP.DATS ),I_TimeSheetRecord.TimeSheetDate), 7, 0)+1

feel free to use

rgds

Jeroen