Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
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!
Answers
-
Hi Brian,
What formula are you using now, could you please share ?
Also is it in OTBI or BI Publisher ?
Regards,
Alex.
0 -
Hi Alex,
OTBI.
I'm currently testing the following formulas which seem to be working, but I haven't tried them on every day yet:
TIMESTAMPADD(SQL_TSI_DAY, -1 - MOD(DAYOFWEEK(CURRENT_DATE) + 2 + 5, 7) - 7, CURRENT_DATE)for two Fridays ago, and
TIMESTAMPADD(SQL_TSI_DAY, -1 - MOD(DAYOFWEEK(CURRENT_DATE-1) + 2, 7), CURRENT_DATE)for last Thursday.
Thanks,
Brian
0 -
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
0 -
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
Regards,
Alex.
0 -
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
Regards,
Alex.
0 -
Hi,
You can also try a conditional like:
==============================
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================================
Regards,
Alex
0 -
Yes, MOD can be used but it can become tricky ( as you see "+2+5" in a MOD7 is just a "full Cycle in MOD7"
Such things might become tricky to control over time) so you can also try a conditional as below which has 2 branches depending if CURRENT_DATE is in the reporting week (days 5,6,7) or you report for previous week ( in days 1,2,3,4)
0 -
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)
END0 -
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
0 -
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
0
