Filtering for Previous Business Day But Also Adjusting for Local Date/Time

[Rosadocc]

Hi,

I have to generate an automated report that pulls yesterdays transactions on a daily basis but it has to be yesterday in Japan (14 hours time difference). The local database time is in US Central Time.

I know how to set up a function in the filter for previous business day. I also know how to adjust the date column to calculate 14 hours back. Where I'm stuck is how to write an expression that does both?

The filter expression I am currently using to pull in previous business day is:
[Views].[General Table].[DateTime_Field] = if (_day_of_week(current_date,7) <=2)
then (_add_days(current_date,-(_day_of_week(current_date,7)+1)))
else (_add_days(current_date,-1))

Is there a way to do this for adjusting for the proper timezone?

All date fields are in date/timestamp format.

[BigChris]

Can't you just add 14 hours to the current date? e.g.

Code Select Expand

[Views].[General Table].[DateTime_Field] = if (_day_of_week(_add_hours(current_date,14),7) <=2)
then (_add_days(_add_hours(current_date,14),-(_day_of_week(_add_hours(current_date,14),7)+1)))
else (_add_days(_add_hours(current_date,14),-1))

[Rosadocc]

Yes I believe that would probably work but upon running the report I get a "Data source adapter error".

This error only comes up when adding the _add_hours function to the expression. Which is strange to me.

I will see what I can come up with.

[Rosadocc]

Still working on figuring this out but I was thinking of taking another approach.

Could I please get help on writing an expression filter for a range of date and time?

The range would be between (2 days ago at 4PM) and (1 day ago 4am). Both are data source timezone.

[BigChris]

Could you do something with two calculated fields?

Code Select Expand

[YourDate] between _add_hours(_add_days(current_date,-2),14) and _add_hours(_add_days(current_date,-1),4)

[Rosadocc]

I continue to get a data source error when mixing _add_hours and _add_days.

For now I got this to work:

Code Select Expand

[date/time field] between
_add_hours(current_timestamp,-28)
and _add_hours(current_timestamp,-13)

The number of hours (28 and 13) are the date range I am looking for. For now this works in pulling in the data for that specific data source date and time range. Unfortunately I'll have to re-work this to apply strictly for business days. If I continue to use this current expression, it will fail on Monday because it will pull from Sunday instead of Friday.

Any ideas on how to calculate for business days?