Show prior day activity expression error

[kac_zrg]

I have the following formula that is not working. The cast expression on it's own works fine, when I add the "_add_days...." i receive an error.

I have also tried the add days without cast and receive an error as well.


cast([PRESENTATION VIEW - COMPANY ANALYSIS].[COMPANY].[dateAdded] as date) _add_days(current_date,-1)

[adam_mc]

Are you trying to show one day prior to your expression?

If so, try:

_add_days(cast([PRESENTATION VIEW - COMPANY ANALYSIS].[COMPANY].[dateAdded] as date), -1)

Hope that helps,
Adam.

[kac_zrg]

Thanks Adam - the filter did not come up with errors, however it is not pulling as needed.

My actual date added (no filters) = Mar 6, 2019 12:21:45 AM
Cast = Mar 6, 2019
with the expression provided = March 5, 2019

I may not have been as clear as to what I am trying to accomplish. I am trying to produce is a report that only shows me records that were entered on the day prior to today. I am still getting all records in the system.

Appreciate your help.

[adam_mc]

So that I can understand more clearly, does your expression cast([PRESENTATION VIEW - COMPANY ANALYSIS].[COMPANY].[dateAdded] as date) return you only a single date (Mar 6, 2019 as that is today) or does it return you a set of multiple dates including the date you need Mar 5, 2019 (if it were run today)?

If it is the former, you will need something else to filter against the value of _add_days(current_date, -1) which will always get the day prior to today.

If it is the latter and you need only the Mar 5, 2019 rows of data, you should be able to add the filter:
cast([PRESENTATION VIEW - COMPANY ANALYSIS].[COMPANY].[dateAdded] as date) = _add_days(current_date, -1)

Hope this helps,
Adam.

[MFGF]

Hi,

The other factor to consider here is that you are mixing database functions and Cognos functions in a single expression. Cast(xxx AS date) is a database function, whereas cast(xxx, date) is a Cognos function. Given that _add_days() is a Cognos function, you might have better success using the Cognos cast function.

Just a thought.

MF.

[kac_zrg]

I thought that Adam's expression below would work because I only want to show the activity for the prior day:

cast([PRESENTATION VIEW - COMPANY ANALYSIS].[COMPANY].[dateAdded] as date) = _add_days(current_date, -1)

However I now receive the following error:

RSV-VAL-0010 Failed to load the report specification. XQE-GEN-0018 Query Service Internal Error has occurred, please see the log for details.

[MFGF]

I thought that Adam's expression below would work because I only want to show the activity for the prior day:

cast([PRESENTATION VIEW - COMPANY ANALYSIS].[COMPANY].[dateAdded] as date) = _add_days(current_date, -1)

However I now receive the following error:

RSV-VAL-0010 Failed to load the report specification. XQE-GEN-0018 Query Service Internal Error has occurred, please see the log for details.

So what result do you get if you use the Cognos cast() function?

MF.

[kac_zrg]

When is use this expression:

cast([PRESENTATION VIEW - COMPANY ANALYSIS].[COMPANY].[dateAdded], date) = _add_days(current_date, -1)

I am still seeing the same error.  Appreciate all the help and suggestions!