Derive Week of year considering the week starts from Saturday

Modified on Wed, 21 Jul 2021 at 11:42 AM

Scenario:


The dataset has sales transactions data with transaction date.


Requirement: 

Derive the week number of the year of each date considering the week starts from Saturday (the week start day can be considered any day in general)

 

 


In Smarten, the week is starting from Sunday. So if you derive the ‘Week of Year’ in normal scenario, it will work as follows.


Example:

Date

Week Of Year 

01-Jan-2021

1

03-Jan-2021

2

10-Jan-2021

3

 

As per the above example, 1st Jan 2021 is Friday. So, the week of year comes under 1 whereas 3rd Jan 2021 is Sunday. Here the next week has started and so the week of year is 2 (as the week of year is starting from Sunday in Smarten as mentioned above).


1. In this example, we are considering the week start day as Saturday. So, in order to achieve the requirement, we need to subtract 6 days from the current date so as to reach the date that falls under Saturday instead of Sunday

  • At first, take a copy of the current date column to perform the subtraction operation by following the steps                  mentioned below:
  •  Right-click on Date column ->Copy ->Column. A new date column will be created (the column has been renamed to  ‘TransformedDate’ in the below example.)

 

       


 

2.  To subtract 6 days from the new date column, we are using the dateAdd() function by following the steps mentioned below:

  • Right click on the copied date column -> Transform -> More ->dateAdd
  • Choose ‘Day’ as Argument 1, -6 as Argument 2, new date column as Argument 3.

 





Date

Days added

Transformed Date

01-Jan-2021

-6

26-Dec-2020

02-Jan-2021

-6

27-Dec-2020

03-Jan-2021

-6

28-Dec-2020

 

The result will subtract 6 days from the current date as shown in the below example.

 


3. Now, extract the week of the year from the newly subtracted date.


Steps:

  • Right-click on the new Date column -> Add column -> Week of Year

 

 

Transformed Date

Week of Year 

26-Jul-2015

30

  20-Mar-2016

11

  24-Dec-2014

52

 


 






This will give the week number of the year considering the week starts from Saturday.


Similarly, you can subtract the number of days as per the following table to start the week from any other specific day. I.e. if you want to consider the week start day like Wednesday, then subtract 3 days from the current date.


Week of Year Start from

Days to be added

Saturday

-6

Wednesday

-3

Monday

-1

Tuesday

-2

Thursday

-4

Friday

-5




Note: This article is based on Smarten Version 5.0. This may or may not be relevant to the Smarten version you may be using.



 

 

 

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article