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 Mon, 04/11/2016 - 07:25

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 Mon, 04/18/2016 - 05:32

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

E.IDEA Sun, 07/24/2016 - 15:21

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?
 
 

Brian Element Mon, 07/25/2016 - 07:27

Hi and welcome to the site.

One of the big problems is date formats.  One way to get around this problem is to create a virtual field to hold the date and use the following equation which looks at the four different possibilities.

@CompIf(@Mid(TRANSACTION_DATE, 3, 1) = "/" .AND. @Mid(TRANSACTION_DATE,6, 1) = "/", @Ctod(TRANSACTION_DATE, "DD/MM/YYYY"), @Mid(TRANSACTION_DATE, 3, 1) = "/" .AND. @Mid(TRANSACTION_DATE,5, 1) = "/", @Ctod(TRANSACTION_DATE, "DD/M/YYYY"), @Mid(TRANSACTION_DATE, 2, 1) = "/" .AND. @Mid(TRANSACTION_DATE,5, 1) = "/", @Ctod(TRANSACTION_DATE, "D/MM/YYYY"), @Mid(TRANSACTION_DATE, 2, 1) = "/" .AND. @Mid(TRANSACTION_DATE,4, 1) = "/", @Ctod(TRANSACTION_DATE, "D/M/YYYY"))

Hope this help.

Brian

Sedem Thu, 07/26/2018 - 10:21

Hi Brian,  I am very new in ideascripting. Apparently I have a date column which  represent date of birth and I want to find exact date after 60years from this date of birth.

Brian Element Thu, 07/26/2018 - 10:53

Hello Sedem,

I came up with this equation for the equation editor that will add 60 years to the date of birth field.  This equation assumes that the date of birth is already a date field.  For this to work create a virtual date field and use the following formula:

@Ctod(@Replace(@str(@year(DOB) + 60, 4, 0) + @str(@Month(DOB), 2, 0) + @str(@day(dob), 2, 0), " ", "0"), "YYYYMMDD")

Just replace the DOB for the name of your field.  What it does it takes the date of birth, strips it down to year, month and day (all of these are in numeric format), adds 60 to the year and then changes the numbers to strings, puts them back together again and then changes the characters to a date.

Hopefully this makes sense.

Brian