Cognos Analytics

 View Only
  • 1.  Comparison of two trends in Cognos Dashboard

    Posted Fri March 17, 2023 05:29 AM

    Hi all,

    We are using IBM Cognos Analytics with Watson (11.2.0).

    Currently we have date, ID, Category, Current_total_record and Last_total_record.

    The category is referring to the trend of Current_total_record and Last_total_record. 

    EG.

    For ID CU003, the trend is increasing as the current total is 60, which increasing from 59 (Last_total_record).

    Is there a way to map the category for current vs category for previous like shown in below example?

    Any help is deeply appreciated.



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------


  • 2.  RE: Comparison of two trends in Cognos Dashboard

    IBM Champion
    Posted Fri March 17, 2023 07:04 AM

    This sounds more like a data prep or query issue than a visualization issue. It looks like the batch date is always on the second of each month. If that never changes ,you could probably get away with making an alias of the table and doing a self join on tbl.batch_dt= add_month(aliastbl.batch_date,-1) and tbl.id = aliastbl.id.

    If you expect IDs to be added or dropped from the table, you may need to make it a full outer join. 

    Once you've done that you'll have two category fields, one for the current period and one for the previous. Just pull those in and it should show you what you're looking for. 



    ------------------------------
    Paul Mendelson
    ------------------------------



  • 3.  RE: Comparison of two trends in Cognos Dashboard

    Posted Wed March 22, 2023 03:40 AM

    Hi Paul,

    Unfortunately the batch date is not always the same. We will process the data on monthly basis that's why the date is the same for each month. But different month will have different date, ranging from second of month to fifth of month.

    For your suggestion, I have some concerns:

    1. If we do full outer join, will data be duplicate?
    2. will it work if the date for different months is not the same? If no, is there a way to just compare the months and year?

    Your suggestion will solve half of the issue. Another half is the value we want to compare is non-consecutive date. This is how I cast the date into varchar

    Year_Month := cast ( _year ( batch_date) ; VARCHAR ( 4 ) ) + '-' + cast ( _month ( batch_date) ; VARCHAR ( 2 ) ) 

    So basically the user will have the option to select any date. For example, if the user select date Jan -2022 and Dec - 2022, the data will treat Jan-2022 and Dec-2022 separately. So basically the X axis will be Current-Category(Dec-2022) while the Y-axis will be Previous-Category(Jan-2022).



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------



  • 4.  RE: Comparison of two trends in Cognos Dashboard

    Posted Mon March 20, 2023 08:14 AM

    Just to confirm, you want to determine the difference between a current value and the last month's value.


    If you have relative time enabled on the measure, those two filters come for free.  You would just need to create a calculation subtracting last month from current month and use that in your heat map.




    Current versus prior varianceexpression for the variance


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



  • 5.  RE: Comparison of two trends in Cognos Dashboard

    Posted Wed March 22, 2023 12:15 AM

    Hi Ian,

    Yes your understanding is correct. But in our case the date is non-consecutive. As in the user can select any date, for example if the user select date Jan -2022 and Dec - 2022, the data will treat Jan-2022 and Dec-2022 separately. So the Current date will be Dec-2022 compare with Nov-2022 for X axis and Previous date will be Jan-2022 compare to Dec-2021 for Y axis.

    Currently in our design we categorized the ID on ETL level. So basically the X axis will be Current-Category while the Y-axis will be Previous-Category.



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------