Run different filter in analysis dependent on day of week. — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Run different filter in analysis dependent on day of week.

Received Response
81
Views
5
Comments
Mik A.M.
Mik A.M. Rank 2 - Community Beginner
edited Aug 13, 2024 6:10PM in Oracle Analytics Cloud and Server

Hello,

I'm wondering if this can be achieved. I apologize in advance as I am a new user to OBIEE.

I'm automating a daily report that pulls previous day's data using field.dateopen = timestampadd(sql_tsi_day,-1,current_date).

I was hoping there would be a way I could, on Monday morning run a report that pulled field.dateopen is between timestampadd(sql_tsi_day,-3,current_date) and timestampadd(sql_tsi_day,-1,current_date).  So when it ran Monday mornings it would pull data from Friday-Sunday.

Is there a way to build an if statement in the filter of an analysis to pull back this data?  I was thinking of creating an if statement of "if dayofweek(current_date) = 2 then pull for the previous 3 days, else pull for yesterday.

I'm hoping to automate just one report as opposed to 2 separate reports that run these.  Any help is appreciated.

Thanks!

Answers

  • Ebin
    Ebin Rank 3 - Community Apprentice

    Hi Mik,

    Try the given formula in Advanced SQL filter. Logic is during Monday's we will subtract 4 from current_date and in the filter we use "greater than".

    "Time"."Date" > CAST(CASE WHEN dayname(current_date)='Mon' THEN timestampadd(sql_tsi_day,-4,current_date) WHEN dayname(current_date)<>'Mon' THEN timestampadd(sql_tsi_day,-2,current_date) END AS DATE)

  • Gianni Ceresa
    Gianni Ceresa Mod

    As Ebin posted your IF in SQL is a CASE WHEN (think at how would you make a SQL query managing that condition).

    If you want to exclude current day values (in case they exists) adapt what Ebin gave you and change into a BETWEEN <the given formula> AND <your timestamp -1> and you get the same result.

    I would just say to adapt a bit the CASE WHEN to remove the 2nd WHEN and make it a ELSE (if because or regional settings some users don't get days of week as "Mon" the provided code will just not work as it casts NULL to a date and so nothing come out). And as your WHEN and your ELSE returns dates (it's a timestampadd) no need to cast the CASE WHEN itself.

    (optimizing your filter will be translated into a faster and smaller SQL filter)

  • Eduardo Tirelli
    Eduardo Tirelli Rank 1 - Community Starter

    Hi Ebin.

    I am also a beginner user...

    How would be the formula syntax if the filter vary betwenn two dates?

    Thank You

  • Ebin
    Ebin Rank 3 - Community Apprentice

    Hi Eduardo,

    You can use "BETWEEN" operator to display the values between 2 dates. The below mentioned example shows date between current_date -4 and current_date.

    "Time"."Date" BETWEEN timestampadd(sql_tsi_day,-4,current_date) AND current_date

    Or else add an "AND" condition to achieve the same.

    "Time"."Date" >= timestampadd(sql_tsi_day,-4,current_date) and  "Time"."Date"<= current_date

  • Eduardo Tirelli
    Eduardo Tirelli Rank 1 - Community Starter

    Hi Ebin.

    I am also a beginner user...

    How would be the formula syntax if the filter vary betwenn two dates?

    Thank You