If you are unable to create a new account, please email support@bspsoftware.com


2 Part Question: Filter Report for Business Day AND Adjust for local date

Started by Rosadocc, 30 Jul 2019 03:54:46 PM

Previous topic - Next topic



I am brand new to using COGNOS and this is my first post. I am currently developing my very first report and need help with two issues.

The data I'm working with is of sales transactions. The report is simply a report a of all transactions for the previous business day. My first issue is how to have the report automatically filter for the previous business day (Monday-Friday) if my date field is in date/time format (i.e. Jul 30, 2019 4:49PM). I came across this formula but it seems to only work if it was simply date field:

[Date 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))

My second issue, which might actually be my first issue now that I think about it, is what formula to use to adjust for Japan local time (+14 hours)? The transactions I deal with are all in US Central Time so I would need to adjust the date/time field to add 14 hours.

Any help, suggestions, or ideas are appreciated!

Francis aka khayman

you are probably using relational datasource... if not there might be an easier way depending on how your time dimension is set up