If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to get previous year

Started by m23, 03 Feb 2008 09:15:59 PM

Previous topic - Next topic

m23

Hi,
I have a boolean style variable and I am trying to setup the expression so that it is database_field = current year minus 1. How do I get the current year minus 1?

ie currently I have [Query].[Field]=2007

I would rather not harcode the 2007 though as otherwise I will need to update it every year. Does any one know what to replace this harcoding with?

aji136

I would use extract(year, current_date) - 1 to get previous year. Not sure if this is what you want...

Suraj

As suggested by aji136,
The following expression should work for your purpose:
[Query].[Date Field]=extract(year, current_date) - 1

m23

I tried putting this in but it gives me an error.

RSV-VAL-0002 Invalid Expression [Query].[Date Field]=extract(year, current_date) - 1 CRX-YXX Parsing error on or around position 42 in the expression "[Query].[Date Field]=extract(year"

or if I run the report

Parsing error on or around position 42 in the expression: "[Query].[Date Field]=extract(year"

Any other ideas?

Suraj

parsing error means you have a ) or , or some characters that are either missing or more than needed.
check the expression.

m23

My expression is exactly as per below

[Cost breakdown].[EFFECTIVE_YEAR]=extract(year, current_date) - 1

position 42 is extract(year, current_date) - 1.

This is in a style variable ie

view -> variables
Then I select a Boolean variable and enter the expression. Alot of the functions such as extract do not seem to be available for me to use here. Surely there must be some way to do it though?


Lokki

try
[Cost breakdown].[EFFECTIVE_YEAR]=extract(year; current_date) - 1

sandhya

first create a data item in ur query with expression
extract(year, current_date) - 1
Then use this data item while comparing in boolean variable



m23

Thanks Sandhya, I finally got it working using your suggestion. :)