Solved

Yearly Date expression in cognos

Posted on 2013-01-18
5
528 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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