Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Yearly Date expression in cognos

Posted on 2013-01-18
5
Medium Priority
?
595 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

610 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