Desktop productivity for business analysts and programmers

How to limit the calculation to three business days excluding weekends

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

How to limit the calculation to three business days excluding weekends


Hello,

I am needing to calculate three business days from OE_DATE & DLVR_STRT_DATE and using the below i am getting three business days between order date & delivery date. however if an order is placed on tuesday then i am getting three business days as wed, thu, fri, sat & sun. I need the sat & sun excluded from the calc as it is > 3 business days. The issue is with orders having order date on tuesdays where the made orders have delivery date on wed, thu, fri, sat & sun where delivery date on sat, sun should be miss as it is > 3 business days. Please advise.

IF intck('WEEKDAY',OE_DATE,DLVR_STRT_DATE) LE 3                       then c = 'made'

else                                                                                                            c = 'miss';


Accepted Solutions
Solution
‎04-02-2014 10:56 AM
Grand Advisor
Posts: 17,405

Re: How to limit the calculation to three business days excluding weekends

Did you try the code included?

expected_delivery=intnx('weekday', OE_DATE, 3);

if DLVR_STRT_DATE>expected_delivery then c='made';

else c='miss';

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: How to limit the calculation to three business days excluding weekends

As you are only looking at a few possibilities, maybe best to just do it with manual if statements, e.g.

If weekday(dlvr_strt_date)=1 then checkdt=dlvr_strt_date + 4; /* Sunday so Mon/Tues/Wed */

If weekday(dlvr_strt_date)=2 then checkdt=dlvr_strt_date + 3; /* Monday so Tues/Wed/Thurs */

...

If weekday(dvlr_strt_date)=6 then checkdt=dlvr_strt_date + 5;      /* Friday so wed */

...

If dlvr_strt_date <= oe_date <= checkdt then "made";

else "miss";

Note-can't check this at the moment.

Grand Advisor
Posts: 17,405

Re: How to limit the calculation to three business days excluding weekends

I am needing to calculate three business days from OE_DATE & DLVR_STRT_DATE

Do you need to calculate the business day that is 3 days away from each of the date above?

Or if the duration between the two dates is greater than 3 days?

Try using intnx to calculate the 3 day delivery date and check if delivery date is after that date.

expected_delivery=intnx('weekday', OE_DATE, 3);

if DLVR_STRT_DATE>expected_delivery then c='made';

else c='miss';

Contributor
Posts: 51

Re: How to limit the calculation to three business days excluding weekends

made = OE_DATE + 3 business days should be less than equal to DLVR_STRT_DATE minus weekends. Please advise.

for example : OE_DATE = Tuesday + 3 business days ( wed, thu, fri ) less than equal to DLVR_STRT_DATE then c='made';

else c='miss';

the below logic works for all days except tuesday where made orders are from wed, thu, fri, sat & sun.

IF intck('WEEKDAY',OE_DATE,DLVR_STRT_DATE) LE 3                       then c = 'made'

else                                                                                                            c = 'miss';

Solution
‎04-02-2014 10:56 AM
Grand Advisor
Posts: 17,405

Re: How to limit the calculation to three business days excluding weekends

Did you try the code included?

expected_delivery=intnx('weekday', OE_DATE, 3);

if DLVR_STRT_DATE>expected_delivery then c='made';

else c='miss';

Contributor
Posts: 51

Re: How to limit the calculation to three business days excluding weekends

It worked Smiley Happy Thank you very much.

Contributor
Posts: 51

Re: How to limit the calculation to three business days excluding weekends

The duration between two date columns should be LE 3 business days.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 281 views
  • 0 likes
  • 3 in conversation