Solved

Yearly Date expression in cognos

Posted on 2013-01-18
5
522 Views
Last Modified: 2013-01-23
Hi,

Can anyone please help me on how to write an automatic expression for yearly date filter.

Year  tarts from OCTOBER . For example  OCTOBER 2012 - OCTOBER 2013 is an year
0
Comment
Question by:new_perl_user
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:SNeupane
ID: 38794821
Is it a hard coded filter or dynamic?
If hard coded, you can simply create a data item called 'Custom Year' with expression like below and use it for date filter:
Date between 10/1/2012 and 10/1/2013

For dynamic/rolling, you have to create calculated data items depending upon how many years you want users to see for data selection.
0
 

Author Comment

by:new_perl_user
ID: 38795438
I am looking for calculated data item for one year.. can you please let me know how to create a calculated expression for this...
0
 
LVL 8

Expert Comment

by:SNeupane
ID: 38797231
if ([Date Field] is between 2012-10-01 and 2013-10-31) then ('Current Fiscal Year') else null

That should work. Give it a try.
You may have to cast your date field if it's not in yyyy-mm-dd format.
If it doesn't work, I'll test at work on Monday and follow up.
0
 

Author Comment

by:new_perl_user
ID: 38805755
The above one is like hard-coding the dates right, I need help for automatic one like below where I am using for Quarterly data.

_first_of_month(_add_months(current_date,-3))
0
 
LVL 8

Accepted Solution

by:
SNeupane earned 500 total points
ID: 38807335
Okay here is the deal.
Create a query calculation for current fiscal year as below:
if([Date Field] between (cast(cast(extract(year from current_date)-1,varchar(4)) || '-10-' ||'01',date)) and (cast(cast(extract(year from current_date),varchar(4)) || '-10-' ||'31',date))) then ([Date Field]) else (null)

It works but you have to take care of null depending upon how the report is laid out.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now