calculate age between two dates excluding saturday and sunday in obiee 11g report

Question

Hi,

how to calculate age between two dates excluding saturday and sunday in obiee 11g report,

used below but getting wrong values

DAYOFWEEK("START DATE")-DAYOFWEEK("END date")-4+

(TIMESTAMPDIFF(SQL_TSI_DAY,

TIMESTAMPADD( SQL_TSI_DAY , 1 - DAYOFWEEK("END date"), "START DATE"),

TIMESTAMPADD( SQL_TSI_DAY , 8 - DAYOFWEEK("START DATE"), "END DATE")))/7*5) ;

Thomas Dodds · Answer

Here's a very recent thread demonstrating why it's not always a good idea to build things in functions on reports ... WEEK_OF_YEAR in OBIEE11g always adds one additional week

RamaYalala · Answer

I have done it through sql and brought it as a view in repository. Not sure if OBIEE functions have this capability. Let me know if you want me to paste the sql code to exclude weekends.

Thomas Dodds · Answer

This is where having a better formed time dimension pays off in spades ... to do this in a report is cumbersome.  It's much easier to count the daily rows between two date rows where weekend_flg = Y ... and it's simple to put the flag in the day grained time table (day numbers 1 and 7) ... my solution is fix the time dimension as this won't be the last time you are asked to do something similar.