Determine previous month value based on Month column

Question

I have a data set with one measure, and two categories (Month, Product). What I want to achieve is the following view in a data module:

I want to have a column which shows me the value of Month-1 based on Month in Column Month

(e.g. May 2023 -> Value from April 2023)

Product	Month	Measure	Calculation (measure from month -1)
A	4	15	-
A	5	23	15
A	6	20	23

Is there an easy code for that or do I need to work with joins and support columns?

I tried already all sorts of Total,Aggregate functions with FOR, PERIOD clauses. I don't want to work with current_timestamp. Formatting is not an issue, as it is recognized as the correct data type.

Answer 1

So what you're looking for in SQL would be:

select Product
, Month
, Measure
, lag(Measure) over (partition by Product order by Month) as Calculation
from ...

You can probably set a default (like '-') to replace NULL. It may depend upon which RDBMS you are using.

You can do that in a Cognos data set in a couple of ways.

1. Create a data set using raw SQL so that you can use LAG().
2. Create a data set as follows:
   1. create a new query (query2) and include a data item using running-count to provide a row number. Sort on Product and Month.
   2. copy the query (query3). Change the running-count column to be running-count + 1
   3. join those two queries into the original query that existed when you created the data set (query1), joining on the running-count columns.
   4. perform calculations as needed to produce NULL or '-' as desired. (like identifying where this and previous rows have a different Product.)

Be sure to schedule the data module to refresh on a schedule.

The second method will run slower because you're running the query twice and having Cognos do all of the work. The advantage is it is developed fully using native Cognos features. Also, you may be able to use the same concepts to perform this all within the data module

Answer 2

You maybe able to use the available calendar in your data module to setup the relative date analysis. https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=analysis-creating-data-module-relative-date

This will give you two measure values, the current measure and the previous month measure value.