Cognos Analytics

 View Only
  • 1.  Last Month (Filter)

    Posted Tue May 04, 2021 11:16 AM
    Hi Everyone,

    I am a brand new user of Cognos.
    Version: IBM Cognos Analytics 11.1 R3

    I am currently trying to create an Expression Definition to filter my DateColumn to view data only from the previous complete month.

    Whenever I try to run it, be it with HTML or Excel, no data is returned; and there are no errors when I validate the expression so I am unsure what is happening.

    CODE:
    [source].[datecolumn] in (_add_months(current_date,-1))

    I have also tried to no avail:
    - = (_add_months(current_date,-1))
    - between (_add_months(current_date,-1)) and (_add_months(current_date,0))

    I would appreciate some guidance or direction

    Cheers

    ------------------------------
    Jackey Tran
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Last Month (Filter)

    IBM Champion
    Posted Tue May 04, 2021 11:29 AM

    Hi Jackey,

    I'd suggest to put both date fields to the level you'd like to compare: month. My approach would be to use the extract function in the filter definition as "extract(month,[source].[datecolumn]) = extract(month, _add_months(current_date,-1) )".

    What kind of data source are you using? Maybe the extract function does not work with everyone.



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------



  • 3.  RE: Last Month (Filter)

    Posted Tue May 04, 2021 11:30 AM
    Hi Jackey
    I think you will need to use first of month and last of month, replace the sysdate with current_date. See below expression
    [source].[datecolumn] between_first_of_month(_add_months(sysdate(),-1)) and _last_of_month(_add_months(sysdate(),-1))
    Thanks
    Sanjay

    ------------------------------
    Sanjay Chakravarty
    ------------------------------



  • 4.  RE: Last Month (Filter)
    Best Answer

    IBM Champion
    Posted Wed May 05, 2021 02:07 AM
    Hi

    If it is a datamodule, you can add the relative calendar to the datamodule, you will get a lot of filters out of the box. 

    https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=modules-relative-date-analysis

    Otherwise the error in you code is you only select 1 date, so the correct code is 

    [source].[datecolumn] between _first_of_month(_add_months(current_date,-1))) and _last_of_month(_add_months(current_date,-1)))

    Br

    ------------------------------
    René Kent Nielsen
    Brand manager
    CogniTech A/S
    Herning
    ------------------------------



  • 5.  RE: Last Month (Filter)

    Posted Wed May 05, 2021 08:17 AM
    Edited by System Fri January 20, 2023 04:37 PM
    Hi, here's what I use (same as Rene):

    [my_date] between _first_of_month(_add_months(current_date,-1)) and _last_of_month(_add_months(current_date,-1))

    Regards, Melody Alberti

    ------------------------------
    MELODY ALBERTI
    ------------------------------