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.

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. 

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).

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.

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.

Original Message:
Sent: Mon March 20, 2023 08:13 AM
From: IAN HENDERSON
Subject: Comparison of two trends in Cognos Dashboard

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.

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