Cognos Analytics

Expand all | Collapse all

Last Month (Filter)

Jump to Best Answer
  • 1.  Last Month (Filter)

    Posted 2 days ago
    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
    ------------------------------


  • 2.  RE: Last Month (Filter)

    Posted 2 days ago

    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 2 days ago
    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

    Posted yesterday
    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 yesterday
    Edited by MELODY ALBERTI yesterday
    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
    ------------------------------