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

Jack

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) ;

Jack

can any one guide on calculating weekdays excluding saturday and sunday in obiee11g report

Thomas Dodds

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.

RamaYalala

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

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