CDS Part 19 – Finding Week of the Year in CDS Views

By
Ruthvik Gautham
-
2
8997

We do not claim we are flawless. We always say, we are “Work in Progress”. We are thankful that we are accepted and followed by even with our limitations. We thank all our followers and patrons for the tremendous response and support over the years in entrusting our ability to crack any problem albeit within our domain expertise and limited scope of exposure.

Recently, I received a problem statement as follows ( bit hesitant to accept the compliments 🙂 )

Thanks to the reader, for the wonderful support and appreciation. I have created a test CDS to answer first of the two statements.

Ignore the Warning and also ignore the from where the data is fetched or what is fetched. Only for demo the above code.

Code Snippet

@AbapCatalog.sqlViewName: 'ZTEST_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Test CDS'
define view ZTEST
with parameters @Environment.systemField: #SYSTEM_DATE
p_curr_date : abap.dats,
@Environment.systemField: #SYSTEM_DATE
p_start_date : abap.dats
as select distinct from sflight as a
{
key a.carrid,
key a.connid,
dats_days_between($parameters.p_start_date,$parameters.p_curr_date) as DaysBtw, div(dats_days_between($parameters.p_start_date,$parameters.p_curr_date), 7) as WeekBtw, mod(dats_days_between($parameters.p_start_date,$parameters.p_curr_date), 7) as ExtraDays, case mod(dats_days_between($parameters.p_start_date,$parameters.p_curr_date), 7) when 0 then div(dats_days_between($parameters.p_start_date,$parameters.p_curr_date), 7) else div(dats_days_between($parameters.p_start_date,$parameters.p_curr_date), 7) + 1 end as WeekofYear
}
where a.carrid = 'AA'
and a.connid = '0017'

Also Read: CDS Part 8. Usage of Built-In Functions in CDS – I

Logic is simple:

Step 1:
Take the input as 1st of Jan of respective year.

Step 2:
Find the difference in the number of days between start date of the year and input date.

Step 3:
Divide the difference in number of days by ‘7’ to get the number of weeks.

Step 4:
Use modulo for same difference in number of days to get extra days.

Step 5:
TWO cases based on modulo.
1) When it is zero. -> Don’t add any extra to calculated number of weeks in step 3.
2) When it is non-zero -> Add extra ‘1’ to calculated number of weeks in step 3.

Thus, solved. 🙂 . Very much like our golden days of schooling. Haha. 😛

Output

Since I am using the present date (for demonstrating the logic), using 1st Jan 2020 as input start date.

Also Read: CDS Part 16. Usage of Built-In Functions in CDS – IV

Calculate Week based on the Date in the Database Table

Now, next challenge was to optimize and have absolutely no external inputs and just based on data from the DB to calculate the week of the year.

Here it is:

@AbapCatalog.sqlViewName: 'ZTEST_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Test CDS'
define view ZTEST
as select distinct from sflight as a
{
key a.carrid,
key a.connid,
key a.fldate,
case mod( dats_days_between( cast( concat( substring(a.fldate, 1, 4) , '0101' ) as abap.dats ), a.fldate ) , 7) when 0 then div( dats_days_between( cast( concat( substring(a.fldate, 1, 4) , '0101' ) as abap.dats ), a.fldate ) , 7) else div( dats_days_between( cast( concat( substring(a.fldate, 1, 4) , '0101' ) as abap.dats ), a.fldate ) , 7) + 1 end as WeekofYear
}

But then this wasn’t the right code. Many hidden bugs exist 🙂 Let me reveal.

1. Which is the starting day of the week that was considered to calculate?
2. Does this code take into consideration leap year?
3. First week of the year was an issue.
4. Wrong assumption that 01st of Jan was the last day of the first week in every year.

For point 1, the dynamic coding would be built which addressed if Sunday was made first day of the week, or Monday, the start of new business week. Similarly, issue 4 was handled here itself.

Issue 2, is addressed since we take into account number of days in between, which of course SAP might have given a thought about.

Issue 3, was fixed through a complex case.

To beat all the above challenge, had to rewrite many things and also create a new set of objects like a new DB Table, Class to update the first Saturday ( to make things dynamic I called it First End Date).

Database:

Global Class to update values in the table:

Values in the table:

One of the best code I have written in sometime now. Why does it qualify for being best?

Reason:
1. There is a complex case statement. It is a nested case example.
2. In the where clause there is usage of substring() function. Unique thing I have done.

Even though the code does look simple, had to burn midnight oil to simplify and turn into a solution.

Final Output

For verification I created a calendar with week count.

Please go through this tutorial again, do hands on code, validate and let us know if you find any bugs or exceptions!!!

Code, Test, Debug .. Repeat. 🙂

Please follow our LinkedIn Page, LinkedIn Group, Facebook Page, Twitter and Instagram.

Save our number +1-646-727-9273 and send us a Whatsapp message ‘LEARN’ to be part of our Learning Community.

Free Step by Step Core Data Services Exercises

Step by Step Virtual Data Model – VDM Tutorials

RELATED ARTICLESMORE FROM AUTHOR

2 COMMENTS

  1. Hi Ruthvik,

    i also had a lot trouble with week numbers in CDS Views.
    Your solution is working and that is most important.
    Here are only some additions:
    – you could have used the table SCAL_TT_DATE and Report VDM_GENERATE_DATES (this is maybe not possilble in every release)
    – parameters in CDS Views are sometimes problematic. I wanted to use the View as association in a different view. To use it you have to put the Parameter in every View and also dynamic field selection is in some cases restricted. I ended up using an AMDP table function CDS. There you can use current_date without an parameter.

    Regards
    Fabian

      • There is also I_CalendarDate ready to use, based on SCAL_TT_DATE. At least the weeknumbers can be easily derived from that. Other information is included as well. Helped me to avoid calculation of the week numbers.

LEAVE A REPLY

Please enter your comment!
Please enter your name here