Working with date, weekday and weekend

15 posts / 0 new
Last post
William Yong
Offline
Joined: 06/30/2014 - 00:37
Working with date, weekday and weekend

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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
Offline
Joined: 06/30/2014 - 00:37

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
Offline
Joined: 11/08/2017 - 11:09

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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
Offline
Joined: 06/30/2014 - 00:37

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
 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi William,

I just tried it and it worked for me.  Is your Issue_Date a date field or character?  It should be date.  Can you give me a screen grab of the error and your equation?

When you get the custom function working then you would just summarize on the week of the year field and extract anything over 3.

Brian

William Yong
Offline
Joined: 06/30/2014 - 00:37

Hi Brian,
My Issue_Date is date field. Please refer to the attached error screen grab. Thanks.
Regards,
William
 
 

Images: 
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

William it returns a numeric and not a character.  Try changing the field type to numeric.

E.IDEA
Offline
Joined: 07/24/2016 - 15:04

Hi, i have the following data in at the same column. Which equation can i use to convert the data at the same date format in IDEA?
 
 

Images: 

Pages