Get the last working day of the previous quarter from a given date

[Bhargavi27]

Hi All,

I am working on requirements where I have to calculate logic always based on the last day of the previous quarter. I have the below date field. and it has what I want. My problem here is, that the Week field has all dates I only want to do the logic based on the last day of the previous quarter. Can someone tell me how to do that?

I don't want to filter dates. I want to apply some logic that gives me the dates what I want

Any help on this is greatly appreciated

Thanks

---

Operating system used: Windows

[Bhargavi27 · Accepted solution]

Thanks so much for your reply. 

In my dataset date field, I don't have any non-working day dates, so, fortunately, I don't need to calculate this. What I require, is to select the last day of the quarter. So for that I did the below steps

1st step: I used formula to truncate my Date to quarter

2nd step: I used group by to get the max of my date field

3rd step: I used the formula again to get the matching between date and date_max and that's it, so for a given quarter when my logic is true I get yes if not no, and based on that I will do my logic.

I don't know if this is correct approach or not but I got what I want. 

Again thanks for the reply. Much appreciated

[Turribeach]

Calculating the last working day of the previous quarter is relatevely easy to do in Pandas (Python Code Recipe) assuming you don't need to handle national holidays. I wasn't sure if you wanted a Code Recipe or a Visual Recipe solution but I felt tempted to try to do it in a Visual Recipe (Prepare Recipe) since according to the documention there are no built-in formula functions to deal with quarters. So here is a formula that you can add a step in a Prepare recipe (Add Step, search for Formula and add the following to Expression) will give you the last weekday of the previous quarter:

if(datePart(inc(inc(asDate(concat(datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'year'), '-', datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'month'), '-1'), 'yyyy-MM-dd'), 1, 'months'), -1, 'days'), 'dayofweek') < 6, inc(inc(asDate(concat(datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'year'), '-', datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'month'), '-1'), 'yyyy-MM-dd'), 1, 'months'), -1, 'days'), inc(inc(inc(asDate(concat(datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'year'), '-', datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'month'), '-1'), 'yyyy-MM-dd'), 1, 'months'), -1, 'days'), mod(datePart(inc(inc(asDate(concat(datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'year'), '-', datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'month'), '-1'), 'yyyy-MM-dd'), 1, 'months'), -1, 'days'), 'dayofweek'), 5) * -1
, 'days'))

Now clearly you are going to struggle to understand what's going there so I am going to explain. In my sample I am using the now() function as my source date but you can replace that with whatever column name you want (such as your Week column) using a Text Editor as long as it is a date data type. How does this long formula works? 

1. We start by calculating the mod(date, 3) as this allows us to know how many months we need to go back to find the previous quarter. So if we are in month 11 for instance mod(11, 3) returns 2 which means that if we do 11 - 2 it will get us month 9 which is the correct month for the previous quarter
2. We then use the mod result to jump to n number of months ago. Then we only take the year and month parts from this date as we are trying to get the last day of the quarter, not any random day. We concatenate the year and month parts to a 1 as the day to be in the first day of the quarter month
3. Now we convert back from string to date and we have date field again with the month of the last quarter but the first day
4. To calculate the last day of the month in an accurate way we can easily add 1 month and then take 1 day away which gives us the last calendar day of the quarter
5. The final step is to then calculate what day of the week we are to go backwards 1 or 2 days (if falling into Saturday or Sunday) if it is not a weekday. We first check if the day of the week is below 6, which means it's Mon-Fri so no changes needed. If it is 6 or 7 we do the mod of that by 5 which gives 2 for Sunday and 1 for Saturday, the exact number of days we need to go back to find a weekday day

With regards to the unreadability of the formula you can vote for the following Product idea which I just raised to improve the formula preprocesor to support code formatting:

https://community.dataiku.com/t5/Product-Ideas/Improve-readability-of-Formula-Preprocesor-by-support...

[Bhargavi27 · Accepted solution]

Thanks so much for your reply. 

In my dataset date field, I don't have any non-working day dates, so, fortunately, I don't need to calculate this. What I require, is to select the last day of the quarter. So for that I did the below steps

1st step: I used formula to truncate my Date to quarter

2nd step: I used group by to get the max of my date field

3rd step: I used the formula again to get the matching between date and date_max and that's it, so for a given quarter when my logic is true I get yes if not no, and based on that I will do my logic.

I don't know if this is correct approach or not but I got what I want. 

Again thanks for the reply. Much appreciated

[Turribeach · Accepted solution]

You asked to "Get the last working day of the previous quarter from a given date" and that's exactly what I have provided you. Now you say you don't need to worry about working days and that you want the last day of the current quarter not the previous one. That to me is a significant change in the requirement of what you asked.

In any case glad you found what you need but it will be much helpful in the future if you specify clearly what you want because otherwise makes help you a lot harder. 

[Bhargavi27]

I agree. Sorry when i was working through those steps and thats when I realized i dont have any non working dates in my dataset. 

once again sorry for the confusion