Cognos Analytics

​Hi,

The new data module with relative time periods is great, but has anyone tried to make more relative periods fx prior month -1 or prior 3 months ?

Br
René

------------------------------
René Kent Nielsen
------------------------------

#CognosAnalyticswithWatson

I want to try this. can this feature enabled on reports that end-user uses? 
Most applications allow end users to generate reports based on such relative date filters. 
https://help.salesforce.com/articleView?id=filter_dates_relative.htm&type=5

------------------------------
Ghanesh PV
------------------------------

Original Message:
Sent: Thu January 31, 2019 03:04 AM
From: René Kent Nielsen
Subject: Relative date

​Hi,

The new data module with relative time periods is great, but has anyone tried to make more relative periods fx prior month -1 or prior 3 months ?

Br
René

------------------------------
René Kent Nielsen
------------------------------

#CognosAnalyticswithWatson

You can create custom relative time filters. See the documentation here

------------------------------
DENNY NAREZNY
------------------------------

Original Message:
Sent: Wed August 07, 2019 11:56 PM
From: Ghanesh PV
Subject: Relative date

I want to try this. can this feature enabled on reports that end-user uses? 
Most applications allow end users to generate reports based on such relative date filters. 
https://help.salesforce.com/articleView?id=filter_dates_relative.htm&type=5

------------------------------
Ghanesh PV

Original Message:
Sent: Thu January 31, 2019 03:04 AM
From: René Kent Nielsen
Subject: Relative date

​Hi,

The new data module with relative time periods is great, but has anyone tried to make more relative periods fx prior month -1 or prior 3 months ?

Br
René

------------------------------
René Kent Nielsen
------------------------------
#CognosAnalyticswithWatson

If anyone has done custom relative time filters and is willing to share the script, I'd love to see it.  We are specifically looking for rolling time.  Ex: Rolling Month AND Rolling Year.

------------------------------
Missy Miller
BI Analyst
ND Information Technology Department
Bismarck ND
------------------------------

Original Message:
Sent: Thu August 08, 2019 04:44 PM
From: DENNY NAREZNY
Subject: Relative date

You can create custom relative time filters. See the documentation here

------------------------------
DENNY NAREZNY

Original Message:
Sent: Wed August 07, 2019 11:56 PM
From: Ghanesh PV
Subject: Relative date

I want to try this. can this feature enabled on reports that end-user uses? 
Most applications allow end users to generate reports based on such relative date filters. 
https://help.salesforce.com/articleView?id=filter_dates_relative.htm&type=5

------------------------------
Ghanesh PV

Original Message:
Sent: Thu January 31, 2019 03:04 AM
From: René Kent Nielsen
Subject: Relative date

​Hi,

The new data module with relative time periods is great, but has anyone tried to make more relative periods fx prior month -1 or prior 3 months ?

Br
René

------------------------------
René Kent Nielsen
------------------------------
#CognosAnalyticswithWatson

@Missy Price Below is a rolling 6 month relative time filter

// This is a template expression that is used by the column property 'Lookup reference'.
// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
// validate: 1 = 1
#$_this.parent.idForExpression# >=
     #queryValue($_this.parent.split.ref + '.PM_TheDate',
                 $_this.parent.split.ref + '.TheDate = ' + 
     queryValue($_this.parent.split.ref + '.PM_TheDate',
                 $_this.parent.split.ref + '.TheDate = ' +
        queryValue($_this.parent.split.ref + '.PM_TheDate',
                 $_this.parent.split.ref + '.TheDate = ' +
          queryValue($_this.parent.split.ref + '.PM_TheDate',
                 $_this.parent.split.ref + '.TheDate = ' +
                      queryValue($_this.parent.split.ref + '.PM_TheDate' ,
                                 $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)))))#
AND
#$_this.parent.idForExpression# <= #$_as_of_date#

​

------------------------------
DENNY NAREZNY
------------------------------

Original Message:
Sent: Fri August 09, 2019 09:43 AM
From: Missy Miller
Subject: Relative date

If anyone has done custom relative time filters and is willing to share the script, I'd love to see it.  We are specifically looking for rolling time.  Ex: Rolling Month AND Rolling Year.

------------------------------
Missy Miller
BI Analyst
ND Information Technology Department
Bismarck ND

Original Message:
Sent: Thu August 08, 2019 04:44 PM
From: DENNY NAREZNY
Subject: Relative date

You can create custom relative time filters. See the documentation here

------------------------------
DENNY NAREZNY

Original Message:
Sent: Wed August 07, 2019 11:56 PM
From: Ghanesh PV
Subject: Relative date

I want to try this. can this feature enabled on reports that end-user uses? 
Most applications allow end users to generate reports based on such relative date filters. 
https://help.salesforce.com/articleView?id=filter_dates_relative.htm&type=5

------------------------------
Ghanesh PV

Original Message:
Sent: Thu January 31, 2019 03:04 AM
From: René Kent Nielsen
Subject: Relative date

​Hi,

The new data module with relative time periods is great, but has anyone tried to make more relative periods fx prior month -1 or prior 3 months ?

Br
René

------------------------------
René Kent Nielsen
------------------------------
#CognosAnalyticswithWatson

Having a hard time to create these relative time filters. Is anyone willing to share the expressions for "Current Date" and "Next 10 Days" (including today)?

------------------------------
Dagur Egonsson
------------------------------

Original Message:
Sent: Fri August 09, 2019 11:02 AM
From: DENNY NAREZNY
Subject: Relative date

@Missy Price Below is a rolling 6 month relative time filter

// This is a template expression that is used by the column property 'Lookup reference'.// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.// validate: 1 = 1#$_this.parent.idForExpression# >=     #queryValue($_this.parent.split.ref + '.PM_TheDate',                 $_this.parent.split.ref + '.TheDate = ' +      queryValue($_this.parent.split.ref + '.PM_TheDate',                 $_this.parent.split.ref + '.TheDate = ' +        queryValue($_this.parent.split.ref + '.PM_TheDate',                 $_this.parent.split.ref + '.TheDate = ' +          queryValue($_this.parent.split.ref + '.PM_TheDate',                 $_this.parent.split.ref + '.TheDate = ' +                      queryValue($_this.parent.split.ref + '.PM_TheDate' ,                                 $_this.parent.split.ref + '.TheDate = ' + $_as_of_date)))))#AND#$_this.parent.idForExpression# <= #$_as_of_date#

​

------------------------------
DENNY NAREZNY

Original Message:
Sent: Fri August 09, 2019 09:43 AM
From: Missy Miller
Subject: Relative date

If anyone has done custom relative time filters and is willing to share the script, I'd love to see it.  We are specifically looking for rolling time.  Ex: Rolling Month AND Rolling Year.

------------------------------
Missy Miller
BI Analyst
ND Information Technology Department
Bismarck ND

Original Message:
Sent: Thu August 08, 2019 04:44 PM
From: DENNY NAREZNY
Subject: Relative date

You can create custom relative time filters. See the documentation here

------------------------------
DENNY NAREZNY

Original Message:
Sent: Wed August 07, 2019 11:56 PM
From: Ghanesh PV
Subject: Relative date

I want to try this. can this feature enabled on reports that end-user uses? 
Most applications allow end users to generate reports based on such relative date filters. 
https://help.salesforce.com/articleView?id=filter_dates_relative.htm&type=5

------------------------------
Ghanesh PV

Original Message:
Sent: Thu January 31, 2019 03:04 AM
From: René Kent Nielsen
Subject: Relative date

​Hi,

The new data module with relative time periods is great, but has anyone tried to make more relative periods fx prior month -1 or prior 3 months ?

Br
René

------------------------------
René Kent Nielsen
------------------------------
#CognosAnalyticswithWatson

Original Message------

Having a hard time to create these relative time filters. Is anyone willing to share the expressions for "Current Date" and "Next 10 Days" (including today)?

------------------------------
Dagur Egonsson
------------------------------

#CognosAnalyticswithWatson

Hi Henk,
I would like to add the relative date filters to the Gregorian calender that comes with CA.

------------------------------
Dagur Egonsson
------------------------------

Original Message:
Sent: Mon September 16, 2019 02:02 PM
From: HENK CAZEMIER
Subject: Relative date

Hi Dagur,

I can give you a hand.
Are you trying to add a filter to the Caledar definition or is it an ad-hoc instance where you want to do this?

The target filter expression would look something like:
<some_date_column> between current_date and _add_days(current_date,10)

Thanks
Henk

Henk Cazemier Data Modeling and Access Architect IBM Analytics Cognos Software Phone: (613) 356-6204 E-mail: Henk.Cazemier@ca.ibm.com

 
 

Original Message------

Having a hard time to create these relative time filters. Is anyone willing to share the expressions for "Current Date" and "Next 10 Days" (including today)?

------------------------------
Dagur Egonsson
------------------------------

#CognosAnalyticswithWatson

Hi Dagur,
The following document describes some detailed steps on how this can be done.
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/t_custom_date_filters.html

If you're interested in doing a rolling 6 months relative to $_as_of_date, which defaults to current_date, then here is an expression that is using the _add_months() macro function.

// This is a template expression that is used by the column property 'Lookup reference'.
// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
// validate: 1 = 1
#$_this.parent.idForExpression# >=
#timestampMask(_add_months($_as_of_date +' 00:00:00',-6),'yyyy-mm-dd')#
AND
#$_this.parent.idForExpression# <= #$_as_of_date#

_add_months macro function requires a timestamp as input, thus it is a concatenation of $_as_of_date and ' 00:00:00'.
The output is also a timestamp, but only the date portion is needed/wanted, the conversion is done using the macro function timestampMask.

Please let me know if you have any questions.
//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Tue September 17, 2019 04:24 AM
From: Dagur Egonsson
Subject: Relative date

Hi Henk,
I would like to add the relative date filters to the Gregorian calender that comes with CA.

------------------------------
Dagur Egonsson

Original Message:
Sent: Mon September 16, 2019 02:02 PM
From: HENK CAZEMIER
Subject: Relative date

Hi Dagur,

I can give you a hand.
Are you trying to add a filter to the Caledar definition or is it an ad-hoc instance where you want to do this?

The target filter expression would look something like:
<some_date_column> between current_date and _add_days(current_date,10)

Thanks
Henk

Henk Cazemier Data Modeling and Access Architect IBM Analytics Cognos Software Phone: (613) 356-6204 E-mail: Henk.Cazemier@ca.ibm.com

 
 

Original Message------

Having a hard time to create these relative time filters. Is anyone willing to share the expressions for "Current Date" and "Next 10 Days" (including today)?

------------------------------
Dagur Egonsson
------------------------------

#CognosAnalyticswithWatson

Hi Henk,
I would be trying to add relative filter for "Current Date" and "Next 10 Days" (including today).
Using your template I tried with

// This is a template expression that is used by the column property 'Lookup reference'.
// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
// validate: 1 = 1
#$_this.parent.idForExpression# >= #$_as_of_date#
AND
#$_this.parent.idForExpression# <= #timestampMask(_add_days($_as_of_date +' 00:00:00',10),'yyyy-mm-dd')#

The expression is valid but when I save the data module I get
"Could not validate module: MSR-GEN-0007 The module "2982111794_fc2551ebfc494460a8a381aca9f755a2_sessionTemp" was not found. "
and 
using it gives me "Query Service internal error has occurred, please see the log for details."

------------------------------
Dagur Egonsson
------------------------------

Original Message:
Sent: Tue September 17, 2019 09:56 AM
From: HENK CAZEMIER
Subject: Relative date

Hi Dagur,
The following document describes some detailed steps on how this can be done.
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/t_custom_date_filters.html

If you're interested in doing a rolling 6 months relative to $_as_of_date, which defaults to current_date, then here is an expression that is using the _add_months() macro function.

// This is a template expression that is used by the column property 'Lookup reference'.
// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
// validate: 1 = 1
#$_this.parent.idForExpression# >=
#timestampMask(_add_months($_as_of_date +' 00:00:00',-6),'yyyy-mm-dd')#
AND
#$_this.parent.idForExpression# <= #$_as_of_date#

_add_months macro function requires a timestamp as input, thus it is a concatenation of $_as_of_date and ' 00:00:00'.
The output is also a timestamp, but only the date portion is needed/wanted, the conversion is done using the macro function timestampMask.

Please let me know if you have any questions.
//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Tue September 17, 2019 04:24 AM
From: Dagur Egonsson
Subject: Relative date

Hi Henk,
I would like to add the relative date filters to the Gregorian calender that comes with CA.

------------------------------
Dagur Egonsson

Original Message:
Sent: Mon September 16, 2019 02:02 PM
From: HENK CAZEMIER
Subject: Relative date

Hi Dagur,

I can give you a hand.
Are you trying to add a filter to the Caledar definition or is it an ad-hoc instance where you want to do this?

The target filter expression would look something like:
<some_date_column> between current_date and _add_days(current_date,10)

Thanks
Henk

Henk Cazemier Data Modeling and Access Architect IBM Analytics Cognos Software Phone: (613) 356-6204 E-mail: Henk.Cazemier@ca.ibm.com

 
 

Original Message------

Having a hard time to create these relative time filters. Is anyone willing to share the expressions for "Current Date" and "Next 10 Days" (including today)?

------------------------------
Dagur Egonsson
------------------------------

#CognosAnalyticswithWatson

Hi Missy - are you using relational or OLAP source?  There are some nice MDX functions that make this a bit easier, but there are also good built-in SQL-based date functions in the Cognos expression builder.  Your approach would be different based on data source structure (OLAP/Cube/DMR vs. Relational)

------------------------------
Albert Valdez
albert@intelalytics.com
------------------------------

Original Message:
Sent: Fri August 09, 2019 09:43 AM
From: Missy Miller
Subject: Relative date

If anyone has done custom relative time filters and is willing to share the script, I'd love to see it.  We are specifically looking for rolling time.  Ex: Rolling Month AND Rolling Year.

------------------------------
Missy Miller
BI Analyst
ND Information Technology Department
Bismarck ND

Original Message:
Sent: Thu August 08, 2019 04:44 PM
From: DENNY NAREZNY
Subject: Relative date

You can create custom relative time filters. See the documentation here

------------------------------
DENNY NAREZNY

Original Message:
Sent: Wed August 07, 2019 11:56 PM
From: Ghanesh PV
Subject: Relative date

I want to try this. can this feature enabled on reports that end-user uses? 
Most applications allow end users to generate reports based on such relative date filters. 
https://help.salesforce.com/articleView?id=filter_dates_relative.htm&type=5

------------------------------
Ghanesh PV

Original Message:
Sent: Thu January 31, 2019 03:04 AM
From: René Kent Nielsen
Subject: Relative date

​Hi,

The new data module with relative time periods is great, but has anyone tried to make more relative periods fx prior month -1 or prior 3 months ?

Br
René

------------------------------
René Kent Nielsen
------------------------------
#CognosAnalyticswithWatson