?
Solved

Yearly Date expression in cognos

Posted on 2013-01-18
5
Medium Priority
?
576 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

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