Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
  • 1.  Last year Sales based on current year

    Posted Wed February 26, 2025 07:51 AM

    Hi,

    I have a report where the sales data is displayed based on the period selected via the prompt page. I would like to add a new column, "Sales (Last Year)," which will display the sales for the same month in the previous year, relative to the period entered in the prompt.

    For example, if the user enters the period as 202502, the "Sales (Last Year)" column will show sales for the period 202412. Similarly, if the user enters 202404 as the current period, the "Sales (Last Year)" column will display sales for the period 202312.

    my current expression is for calculating the sales is

    tOTAL(IF(  [Period Num]= ?Period? and [Country] = 'USA') THEN (Sales) ELSE (0)) 

    Could you please modify this expression for last year last month period?

    Thanks in advance.



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


  • 2.  RE: Last year Sales based on current year

    Posted Thu February 27, 2025 02:42 AM


    It is the same as for months. You just replace function _add_months with the function _add_years.
    IF 
    (
             _MAKE_TIMESTAMP( 
                      CAST(  SUBSTRING( Period, 1, 4 ), INTEGER  ),
                      CAST(  SUBSTRING( Period, 5, 2 ), INTEGER  ), 
                      1
             )
             =
                             _ADD_YEARS(
             _MAKE_TIMESTAMP( 
                      CAST(  SUBSTRING( ?pMonth?, 1, 4 ), INTEGER  ),
                      CAST(  SUBSTRING( ?pMonth?, 5, 2 ), INTEGER  ), 
                      1
             )
                                , -1 )
    ) THEN ( Sales ) ELSE ( 0 )



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



  • 3.  RE: Last year Sales based on current year

    Posted Thu February 27, 2025 03:57 AM

    Thanks for your reply. I tried your expression but is not working. Could you please update expression "tOTAL(IF(  [Period Num]= ?Period? and [Country] = 'USA') THEN (Sales) ELSE (0)) " so it will show Sales figure for Last year Dec month only.



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



  • 4.  RE: Last year Sales based on current year

    Posted Fri February 28, 2025 09:51 AM

    Or you could you could use _add_months ( <date_field> ) - 12



    ------------------------------
    Dion Paul
    Cognos Administrator
    Ascend SC
    Houston TX
    dpaul@scgts.com
    ------------------------------



  • 5.  RE: Last year Sales based on current year

    Posted Thu February 27, 2025 02:44 AM

    Hi Max, 

    Again for relational data sources the most sustainable solution I can imagine would be a data module using relative date measure filters with a parameter. I tried to find a smooth post regarding relative date filters but didn't succeed. @IAN HENDERSON did you write a blog post or documentation about this? 

    Best, 

    Philipp



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



  • 6.  RE: Last year Sales based on current year

    Posted Thu February 27, 2025 09:58 AM
    Edited by IAN HENDERSON Thu February 27, 2025 09:59 AM
    No, I have not written anything about the topic.  
     
    Here is some documentation about it, including some about how to customize relative time filters.
     
    https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=modules-relative-date-analysis
     
    https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=analysis-creating-relative-date-filters
     
     
    Knowing the data source you are using could be helpful.  In some cases there is functionality to define relative time in some manner, which would allow you to not try to reinvent the wheel.
     
    As Philipp has stated, data modules have functionality to define relative time filters. Dynamic cubes and PowerCubes too.  FM models do not but there are ways to deal with filtering a report to a set of time periods.  Milan has provided an example. Two actually.  Also Trevor has suggested something but you have not indicated how it is not appropriate to your situation.
     
    Knowing what metadata is available to you could be helpful to how you approach the problem but I think the pattern which Milan has given you could be appropriate to your circumstances.
     
    I think it is of some importance to point out that 'it is not working' or similar statements do not provide sufficient information to anyone to diagnose the situation and provide further advice.
     
    In addition, I'm not entirely sure I can distinguish, other than in superficials, the request which you have made here and that which you made in https://community.ibm.com/community/user/businessanalytics/discussion/current-month-vs-previous-month. 



    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 7.  RE: Last year Sales based on current year

    Posted Thu February 27, 2025 11:37 AM

    @IAN HENDERSON The more I get used to relative date filters (copy & paste & adjust for each use case) the more I consider unions and multiple queries as archaic and not sustainable. Relative date filters are defined ONCE and can be applied to ALL measures with 4 clicks only. And they are wokring for all related reports really the same way. 

    What I don't understand is why you restricted this functionality to date columns only. There is also potential for other use cases like measure by status etc. I often convert non-date-values to dummy-dates and back in the mapping-table to use measure filters. 



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



  • 8.  RE: Last year Sales based on current year

    Posted Fri February 28, 2025 02:25 AM

    @Philipp Hornung you probably know this, others may not...

    You can trick relative date functionality to work for any column by using the  validate option. No need to convert anything to a date.

    For example, if in Go Sales I want to create measures for different product lines.

    Firstly I create filters on the product dimension for each product line such as this:  

    I can then edit the Product Line data item and add in a validate to make the DM think it's a date.

    You can see this makes the DM think it's a date:  

    You can then create relative measures as usual. Such as:  

    Once it's all up and running, remove the validate.



    ------------------------------
    Marc Reed
    Reporting Lead
    ------------------------------



  • 9.  RE: Last year Sales based on current year

    Posted Fri February 28, 2025 03:58 AM

    @Marc Reed Wow, great! How did you figure this one out? Thank you very much (again)! 

    @IAN HENDERSON So it's only a validation to bypass. I suggest you tell product management this is your idea and you will enhance the functionality a lot for a broad group of users by a no-brainer-trivial-code-change :-) 



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