Oracle Communities

Oracle Analytics and AI

Child Item

Selecting Previous Thursday/Preceding Friday, regardless of run date

I'm trying to set an analysis that defaults to run for the previous payweek (which for us is Friday to Thursday), no matter what day of the week it's run on.

So for instance if run today, Tuesday 4/16, one formula would select for the previous Thurday (4/11), and another formula would select the Friday before that (4/5). But then, if run this Friday (4/19), the dates would change to 4/18 and 4/12 respectively.

I've tried several different timestampadd formulas, to no luck. Is this possible?

Thanks for helping out someone new to this!

Find more posts tagged with

Analysis

OTBI

Accepted answers

All comments

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

================================================

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

================================================

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END

Alexandru Burcea-Oracle

Hi,

You can also try a logic similar to below one

================================================

CASE WHEN

DAYOFWEEK(CURRENT_DATE)>=5

THEN TIMESTAMPADD(SQL_TSI_DAY, 5-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE

ELSE TIMESTAMPADD(SQL_TSI_DAY, -2-DAYOFWEEK(CURRENT_DATE) , CURRENT_DATE)

END