COGNOS - Days Between Excluding Weekend

2.2k views Asked by At

I Would Like To Count The Number Of Days Between Start Day [Start] and End Date [Finish];

My Current Code Works Fine, But Includes Weekends,

_days_between (IF ([Finish] is missing) THEN (current_date) ELSE (cast([Finish] as date)),cast([Start] as date)) + 1

Edit (Based On Alexey Baturin Answer):

1 + 5 * (_days_between (cast([Finish] as date);cast([Start] as date)) 
     -_day_of_week (cast([Finish] as date);1)+_day_of_week (cast([Start] as date);1))/7 
- if (_day_of_week (cast([Start] as date);1) > 5) then (6) else  (_day_of_week (cast([Start] as date);1)) 
+ if (_day_of_week (cast([Finish] as date);1) > 5) then (5) else (_day_of_week (cast([Finish] as date);1))

I Now Have This Code But This Doesn't Include If [Finish] Is Missing.

1

There are 1 answers

5
Alexey Baturin On BEST ANSWER

Try

1 + (_week_of_year (IF ([Finish] is missing) THEN (current_date) ELSE (cast([Finish] as date))) - _week_of_year (cast([Start] as date))) * 5
- if (_day_of_week (cast([Start] as date),1) > 5) then (6) else  (_day_of_week (cast([Start] as date),1)) 
+ if (_day_of_week (IF ([Finish] is missing) THEN (current_date) ELSE (cast([Finish] as date)),1) > 5) then (5) else (_day_of_week (IF ([Finish] is missing) THEN (current_date) ELSE (cast([Finish] as date)),1))

Or simpler, to make the idea of calculation clear.

1 + (_week_of_year ([Finish]) - _week_of_year ([Start])) * 5
- if (_day_of_week ([Start];1) > 5) then (6) else  (_day_of_week ([Start];1)) 
+ if (_day_of_week ([Finish];1) > 5) then (5) else (_day_of_week ([Finish];1))

End of year bug fixed

1 + 5 * (_days_between ([Finish];[Start]) 
         -_day_of_week ([Finish];1)+_day_of_week ([Start];1))/7
- if (_day_of_week ([Start];1) > 5) then (6) else  (_day_of_week ([Start];1)) 
+ if (_day_of_week ([Finish];1) > 5) then (5) else (_day_of_week ([Finish];1))