Working with date, weekday and weekend

William Yong

Forums

Hi guys,
I'm currently working with date field where I need to determine whether the particular date is either weekday or weekend. I'm able to do that with the @workday(date) function. However, I'm facing difficulty to group that result by week. E.g 04.01.16 to 10.01.16 (1st week in Jan 2016), 11.01.16 to 17.01.16 (2nd week in Jan 2016). My 1 week here starts from Monday to Sunday. 
My task is after the grouping of the date by weeks, I need to extract those transaction that is more than 3 times in a week.
Any idea on how to work that out or somebody could assist me here. Thank you in advance.
Regards,
William

Brian Element Thu, 04/07/2016 - 07:35

Hi William, here are two custom functions that should help you out. The first will return the week of the year assuming a Sunday start of the week and the second assumes a Monday start of the week.  Then you could summarize on week number to get your result.  If your file is over multiple years you should probably include the year.

Hope this helps out.

Brian

William Yong Thu, 04/07/2016 - 23:31

Hi Brian,
Thanks for responding. How am I able to execute the files that you provided. Open in IDEA or in Excel? I try open it in IDEAScript (return error) and it won't execute also in Excel. Please advice.
So assuming my start of the week is Monday, I need to execute the 2nd function, right?
Do you think you could work up a script for me that could request user to insert their files and execute the required function, that is, grouping by date and return in weeks? 
Cheers, William
 

mail.jamy Wed, 10/31/2018 - 04:42

Hey Brian,
 
Thanks for sharing the functions.
 
I used weekMonStart.ideafunc to identify the week# of the 2017 calendar year. Unfortunately, the date 1/1/2017 returned '0' using the "weekMonStart.ideafunc". I was expecting it to be the first week of the year - my first week will have only one day in this year.
 
Can you please share your thoughts?
 
 
Best,
James.

Brian Element Sun, 11/04/2018 - 08:42

Hi James, it is because it is expecting an IDEA date field which has two digits for the day and two for the month.  You will need to reformat the date so that it is a date field.

Brian Element Mon, 11/26/2018 - 11:00

Sorry for not getting back sooner.  I found the problem and I made it more generic.  This custom function takes a date field and a second parameter that indicates the start of the week with 1 being Sunday and 7 being Saturday.

 

Brian Element Fri, 04/08/2016 - 07:52

Hi William,

Download the files and place them in your Custom Functions.ILB folder for the project you are working on (assumes you are using IDEA 9 or 10), it you are using 8 let me know and I will give you the insturctions.

Now these functions should be available in the equation editor.  So create a virtual numeric field, open up the equation editor and type the # to see a list of your custom functions, they work the same as the @ functions. so to use the weekMonStart just enter #weekMonStart(MY_DATE_FIELD) and it will return the week number.

Brian

William Yong Mon, 04/11/2016 - 02:27

Hi Brian,
Thanks and I'm able to insert my custom function. However, it prompted me 'Incorrect number of parameter' error after I key-in #weekMonStart(Issue_Date). Probably I send you the actual file (as uploaded) and could you please assist me here. From the given period, I need to extract those personnel who redeem serial number > 3 times per week. My week start from Monday to Sunday. For example, my first date from the uploaded file is 10/4/13 in 2013 and my task is to group those date by week, i.e. 10/4/13-14/4/13 as 1 week, 15/4/13 to 22/4/13 as another week and so on. From the identified weeks, to perform analysis to extract member who redeem voucher > 3 times per week.   I have added the year and the date return as day field, however, I'm stuck and not quite sure how to proceed with my analysis. Do we need to prepare some idea script here?
Some help here is greatly appreciated. Thanks in advance.
William