Print Page - Return max Year/Month as 'Y' rest of months as 'N'

Title: Return max Year/Month as 'Y' rest of months as 'N'
Post by: kmcgilli on 14 Jan 2020 10:53:31 AM

I am trying to filter on just the maximum month of my dataset and using this:

(Year_Month = maximum ( Year_Month ))
then ('Y')
else ('N')

is not returning 'Y' for the maximum month...

Year_Month looks like this:

201811, 201812, 201901, 201902 etc.

Title: Re: Return max Year/Month as 'Y' rest of months as 'N'
Post by: ArnaudStephanHTH on 15 Jan 2020 10:08:06 AM

I would create a new data item to store the maximum value of Year_Month (and call it MAX_Year_Month or something), and then filter using that.

And then you could create another data item with the formula

CASE WHEN Year_Month = MAX_Year_Month THEN 'Y' ELSE 'N' END

Title: Re: Return max Year/Month as 'Y' rest of months as 'N'
Post by: seb24c on 16 Jan 2020 01:08:26 PM

It looks like you just need to make sure it's looking for the maximum of the whole query.

if ( Year_Month = maximum ( Year_Month for report ) ) then ( 'Y' ) else ( 'N' )

COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Reporting => Topic started by: kmcgilli on 14 Jan 2020 10:53:31 AM