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.
Can't you just add 14 hours to the current date? e.g.
[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))
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.
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.
Could you do something with two calculated fields?
[YourDate] between _add_hours(_add_days(current_date,-2),14) and _add_hours(_add_days(current_date,-1),4)
I continue to get a data source error when mixing _add_hours and _add_days.
For now I got this to work:
[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?