lead and lag

[dougp]

Cognos Analytics includes many windowing aggregation functions - minimum (MIN), maximum (MAX), total (SUM), etc.  Have Lead and Lag been included yet?  I'm using 11.2.4 and still not finding these.

[cognostechie]

You can write your own SQL and use Lead ( <column name> ,1,0 ) over ( order by <column name> ) ;)

[dougp]

I know.  But I was hoping to avoid custom, raw SQL.

[dougp]

Today I noticed that Cognos Analytics 12.1.1 includes "Enhanced support of the LAG and LEAD functions" ( https://www.ibm.com/docs/en/cognos-analytics/12.1.x?topic=components-enhanced-support-lag-lead-functions ).  But what do they mean by "Enhanced"?  Does that mean it's already there?

I see in 12.1.1, on the Functions tab in the FM expression editor, lag and lead are included under Summaries right after count.  But in 12.0.4, I don't see it listed in either Reporting or in FM.  But...

I created a simple query against linear reference data and included

Code Select Expand

lag([Item to Report], 1, null for [Grouping Item] order by [Sorting Item])
...and it works.

The Information box contains this for lag:

lag ( member , index_expression )
Returns the sibling member that is "index_expression" number of positions prior to "member".

Example: lag ( [Tents] , 1 )
Result: Cooking Gear

Example: lag ( [Tents] , -2 )
Result: Packs

I'm guessing that description is appropriate when using dimensional models.  I use only relational models.

Back to the 12.1.1 docs...  Take a look at the images.  You'll need to zoom your browser window a bunch because the image is horrible.  (Even after zooming, I couldn't get an OCR to convert it to text.)  But IBM provided a really helpful example for this function.  That's a vast improvement over examples for other functions, like count...

count ( [ all | distinct ] expression [ auto ] )
count ( [ all | distinct ] expression for [ all|any ] expression { , expression } )
count ( [ all | distinct ] expression for report )
Returns the number of selected data items excluding null values. Distinct is an alternative expression that is compatible with earlier versions of the product. All is supported in DQM mode only and it avoids the presumption of double counting a data item of a dimension table.

Example: count ( Sales )
Result: Returns the total number of entries under Sales.

...where they provide the documentation but the example provides zero insight into how the options affect the output.

I'm already using 12.0.4, but for the benefit of others:  Does anyone here know what version introduced lag and lead?