Run different filter in analysis dependent on day of week.

Question

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!

Eduardo Tirelli · Answer

Hi Ebin.

I am also a beginner user...

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

Thank You

Ebin · Answer

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 · Answer

Hi Ebin.

I am also a beginner user...

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

Thank You