Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
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
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
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.
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?
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:
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
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.
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