Topic Thread

Expand all | Collapse all

Relative date

  • 1.  Relative date

    Posted Thu January 31, 2019 03:05 AM
    ​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
    ------------------------------


  • 2.  RE: Relative date

    Posted Thu August 08, 2019 09:24 AM
    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
    ------------------------------



  • 3.  RE: Relative date

    Posted Thu August 08, 2019 04:45 PM
    Edited by DENNY NAREZNY Thu August 08, 2019 04:45 PM
    You can create custom relative time filters. See the documentation here

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



  • 4.  RE: Relative date

    Posted Fri August 09, 2019 09:44 AM
    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
    ------------------------------



  • 5.  RE: Relative date

    Posted Fri August 09, 2019 11:03 AM

    @Missy Miller 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
    ------------------------------



  • 6.  RE: Relative date

    Posted Mon September 16, 2019 01:16 PM
    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
    ------------------------------



  • 7.  RE: Relative date

    Posted Mon September 16, 2019 02:03 PM
    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


     
     






  • 8.  RE: Relative date

    Posted Tue September 17, 2019 04:25 AM
    Hi Henk,
    I would like to add the relative date filters to the Gregorian calender that comes with CA.

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



  • 9.  RE: Relative date

    Posted Tue September 17, 2019 09:57 AM

    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
    ------------------------------



  • 10.  RE: Relative date

    Posted Tue September 17, 2019 12:10 PM
    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
    ------------------------------



  • 11.  RE: Relative date

    Posted Mon September 16, 2019 03:59 PM
    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
    ------------------------------