0

How do I exclude the February 29 - Leap Year in a Date column in Cognos 10 Report Studio?

This is the column that I'm using for my date prompt

enter image description here

Below is the prompt that I applied on the date column enter image description here

Depicted below is the details and properties of the date prompt that I will use to filter my report using the date prompt. enter image description here

So all attributes and measures in the report are filtered using the start date and end date of the expiration date column I used on the report. How do I avoid the February 29th to add up in my calculated columns (measures)? I want to get all calculation from January 1 to Dec 31 without february 29 that happens every four years? The February 29 data may or may not include a large amount but it has a ripple effect at the year end calculation. That's why I want to exclude Feb 29 included in the computation.

rgstamayo
  • 163
  • 12
BongReyes
  • 205
  • 2
  • 7
  • 19
  • 1
    Why do you want to exclude Feb 29th? What about transactions on that day? I'm sure you could come up with some logic like `and month() <> 2 and day() <> 29`. But I'm still confused on why you'd exclude it. – Andrew Aug 11 '15 at 19:23
  • As per the Business Analyst, the users want to exclude the February 29th data. I cant use the (date column expression) <> 29 because the column is using Feb 29, 2015 date format as it is was tweaked using CAST in my first screenshot. – BongReyes Aug 11 '15 at 19:29

2 Answers2

1

Here's a filter expression that will exclude "leap days":

not (extract(month,[Policy Expiration Date]) = 2 and extract(day,[Policy Expiration Date]) = 29)
Johnsonium
  • 2,005
  • 1
  • 13
  • 15
  • Thanks. Is this suppose to be a Not in function? So I'll just drag [Policy Expiration Date] in the filter section and modify it to this right? – BongReyes Aug 12 '15 at 14:07
  • No. The not operator in this case negates the result of the Boolean operation within the parentheses. If you just put the contents of the parentheses as the filter then it would only return Feb. 29. The not flips the logic returning everything except Feb. 29. – Johnsonium Aug 12 '15 at 15:49
  • I just discovered that the report that I was modifying is using (12 * _days_between([Policy Expiration Date],[Policy Effective Date]))/365 is there any way to apply your answer to this? Thank you in advance – BongReyes Aug 13 '15 at 15:59
  • So are you asking how to drop 2/29 from the calculation if it occurs between [Policy Effective Date] and [Policy Expiration Date]? If so what I would suggest is to perform two calculations, one for leap years and the other for non-leap years. For the calculation when 2/29 occurs in the range, simply subtract 1 from the result of the _days_between() function. What kind of time scale are we dealing with? Years? Months? – Johnsonium Aug 13 '15 at 21:13
  • On further thought, the filter in my answer should remove all 2/29 records from the resultset and thus your calculation as well. – Johnsonium Aug 14 '15 at 16:11
0

Kindly try this.

([Policy Expiration Date] between ?Expiration Start Date? and ?Expiration End Date?)

AND

to_char([Policy Expiration Date],'mmdd') <> '0229'

rgstamayo
  • 163
  • 12
  • Thank you. So I'll just add the AND to_char([Policy Expiration Date],'mmdd') <> '0229' in the prompt? – BongReyes Aug 12 '15 at 14:09
  • This will only work on DB2 and other data sources that support the to_char function. It's not a standard Cognos function. – Johnsonium Aug 12 '15 at 15:51
  • @BongReyes yes just add it in the filter. By the way Johnsonium is correct. I'm not sure what database you are using, but might as well give it a try? Tell me if it doesn't, so I can give you another work around – rgstamayo Aug 14 '15 at 05:38