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
Hi and welcome to the site.
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
Hello Sedem,
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
Hi William,
In reply to Hi Brian, by William Yong
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