Cognos Analytics

 View Only

 Report - Detailed filter - Calculate dynamically a date

Anonymous User's profile image
Anonymous User posted Fri June 27, 2025 12:32 AM

Hello,

I am trying to create a specific detailed filter:

[YYYY].[XXXX].[DateCourte].[DateCourte] = ZZZZ

DateCourte is in the following format:

          "type": "calculated",
          "name": "DateCourte",
          "dataType": "string",
          "isDataTypeInferred": true,
          "expression": "format('Entete-Histo'[Date],\"dd/mm/yyyy\")"

If I replace ZZZZ by ?Param1?, the following dates are proposed as an input parameter : 01/02/2025, 01/03/2025...

=> everything is fine

However, I would like to calculate dynamically ZZZZ to be equal to current day less 1 year : for example, 27/06/2024.

As a consequence, each time I execute the report, the date is filtered on the current day less 1 year.

Is it possible?

Regards

Jeam Coelho's profile image
Jeam Coelho IBM Champion

Hello,

The function _add_years ( date_expression; -1) does not work for you?

Jeanie Krieger's profile image
Jeanie Krieger

Yann,

Because your date is in a string format, you would need to cast your object to be in a date format and then use Jeam's suggestion of _add_years function.

Example:  

cast([YYYY].[XXXX].[DateCourte].[DateCourte], AS DATE) =  _add_years ( current_date; -1)

Alternatively

TO_DATE([YYYY].[XXXX].[DateCourte].[DateCourte], 'YYYY-MM-DD') =  _add_years ( current_date; -1)

Jeanie

Anonymous User's profile image
Anonymous User

Hello,

I am sorry but I am getting the same error:

Regards

Cedric Waras's profile image
Cedric Waras

TO_DATE([ID-KPI].[Entete-Histo].[DateCourte].[DateCourte]; 'YYYY-MM-DD') =  _add_years ( current_date; -1)

Marc Reed's profile image
Marc Reed IBM Champion

Looks like double ]] at the end of DateCourte?