Cognos Analytics

 View Only
  • 1.  current month vs previous month

    Posted 17 hours ago

    Hi,

    I am working on creating a report where I need to display sales for the current month in one column (which I can easily do using the following expression):

    TOTAL(IF(Period = ?pMonth? THEN (Sales) ELSE (0)))

    However, I am struggling with how to write an expression for displaying sales from the previous two months. For example, if the selected month (pMonth) is '202501', I would like to show the sales for '202411'. Could anyone kindly assist me with writing this expression for the last month's data?



    ------------------------------
    Max Ray
    ------------------------------


  • 2.  RE: current month vs previous month

    Posted 13 hours ago
    I think this should work.
    IF 
    (
    _MAKE_TIMESTAMP(
            CAST(  SUBSTRING( Period, 1, 4 ), INTEGER  ),
    CAST(  SUBSTRING( Period, 5, 2 ), INTEGER  ),
    1
    )
    =
                    _ADD_MONTHS(
    _MAKE_TIMESTAMP(
    CAST(  SUBSTRING( ?pMonth?, 1, 4 ), INTEGER  ),
    CAST(  SUBSTRING( ?pMonth?, 5, 2 ), INTEGER  ),
    1
    )
    , -1 )
    ) THEN ( Sales ) ELSE ( 0 )


    ------------------------------
    Milan Milovanovic
    ------------------------------



  • 3.  RE: current month vs previous month

    Posted an hour ago

    Hi Max,

    If your report is based on a data module this is a typical case for measure filters aka relative date calendar in combination with a parameter. If you are considerinfg this I can share an example of an according filter.

    Best, 

    Philipp



    ------------------------------
    Philipp Hornung
    Product Owner & BI Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------