Author Topic: Filtering for Previous Business Day But Also Adjusting for Local Date/Time  (Read 193 times)

Offline Rosadocc

  • Full Member
  • ***
  • Join Date: Jul 2019
  • Posts: 7
  • Forum Citizenship: +0/-0
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.

Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,178
  • Forum Citizenship: +86/-0
Can't you just add 14 hours to the current date? e.g.

Code: [Select]
[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))

Offline Rosadocc

  • Full Member
  • ***
  • Join Date: Jul 2019
  • Posts: 7
  • Forum Citizenship: +0/-0
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.

Offline Rosadocc

  • Full Member
  • ***
  • Join Date: Jul 2019
  • Posts: 7
  • Forum Citizenship: +0/-0
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.

Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,178
  • Forum Citizenship: +86/-0
Could you do something with two calculated fields?

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

Offline Rosadocc

  • Full Member
  • ***
  • Join Date: Jul 2019
  • Posts: 7
  • Forum Citizenship: +0/-0
I continue to get a data source error when mixing _add_hours and _add_days.

For now I got this to work:
Code: [Select]
[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?

 


       
Twittear