Month Filter

[Kid_in_Cognos]

Hi,

My data values are like below.


Months   Start Date     End Date

Mar-16   1-Mar-16     1-Mar-17
Mar-16   1-Jan-16     31-Dec-18
Mar-16   1-Jan-16     31-Dec-17
Mar-16   1-Jan-16     31-Dec-18

I want to check if the Months column values are equal or in between start date and end date. As Date formats are different I am feeling difficulty to achieve this . Can you guide me how to do it?

Regards

[Lynn]

Hi,

My data values are like below.


Months   Start Date     End Date

Mar-16   1-Mar-16     1-Mar-17
Mar-16   1-Jan-16     31-Dec-18
Mar-16   1-Jan-16     31-Dec-17
Mar-16   1-Jan-16     31-Dec-18

I want to check if the Months column values are equal or in between start date and end date. As Date formats are different I am feeling difficulty to achieve this . Can you guide me how to do it?

Regards

The first question is how much of the month needs to be in the range.

Mar-16 runs from the 01-Mar-16 to 31-Mar-16, so if the end date is (let's say) 18-Mar-2016 then is it between or not? Similarly, what if the start date is 5-Mar-16?

If the entire month must be within the range then I'd suggest you convert your Months value to a Month Start and Month End date. If this is a string field then you'll need to figure out the best way to cast it to a date data type for the 1st day of the month. There are built-in Cognos functions for casting, or you can use native database functions. It might be worth posting what database you are using to get specific advice.

There is a function called _last_of_month that you can use to easily find out the last day of the month, using your newly derived first of month value from the previous paragraph.

After that it is just a matter of comparing to see if the entire month is within the range. If the requirement isn't for the entire month then you'll need to provide some details on exactly what constitutes "between start date and end date."

Code Select Expand


[Months Start Date] between [Start Date] and [End Date]
and
[Months End Date] between [Start Date] and [End Date]


[Kid_in_Cognos]

Hi Lynn,

Thanks for the reply.

Yeah. The range is for entire Month only and the database is Oracle. I will try to follow the suggestion and hope to succeed. :)

Cheers !!!

[Lynn]

Hi Lynn,

Thanks for the reply.

Yeah. The range is for entire Month only and the database is Oracle. I will try to follow the suggestion and hope to succeed. :)

Cheers !!!

If memory serves, you might be able to use Oracle's TO_DATE function to get the first day of the month. Haven't been in the Oracle realm in quite some time so take that with a grain or two of salt.

Code Select Expand


TO_DATE( '01-' || [Months], 'DD-MON-YY' )


[Kid_in_Cognos]

Thanks Lynn.
I am trying to total the premium, if the record_creation_date between risk start date and risk end date . But I am getting exception error. Not sure what I am doing. Can you help me out?

case when
([RECORD_CREATION_TS] between [RISK_PE_START_DT] and [RISK_PE_END_DT])
then
(Sales and Claim Summary].[Sales and Cliam Summary].[PREMIUM])
end

Data is as like below :

RECORD_CREATION_TS     RISK_PE_START_DT                 RISK_PE_END_DT
Mar 28, 2016 12:00:00 AM     Jan 1, 2016 12:00:00 PM    Dec 31, 2017 12:00:00 PM
Mar 28, 2016 2:14:12 AM     Jan 1, 2016 12:00:00 PM    Dec 31, 2017 12:00:00 PM
Jun 10, 2016 6:20:44 AM     Jan 1, 2016 12:00:00 PM    Dec 31, 2018 12:00:00 PM
Mar 28, 2016 12:00:00 AM     Mar 1, 2016 2:18:16 AM    Mar 1, 2017 2:18:16 AM


Regards

[Lynn]

What is the error?

I don't see anything wrong with the expression, although adding "else 0" might be something to consider. I'd also set the aggregate function for that expression to total.

Posting the specific error message, including the details, would be helpful in diagnosing.

[Kid_in_Cognos]

Hi Lynn,

I am extremely sorry for my mistake. In case condition after the 'then' stmt ,I missed a bracket somehow.
it was
(Sales and Claim Summary].[Sales and Cliam Summary].[PREMIUM])
the correct one should be
([Sales and Claim Summary].[Sales and Cliam Summary].[PREMIUM])

Thank you much for the help.

[Kid_in_Cognos]

Hi,

The Month column value is like below. My db is Oracle.

MONTHS
---------
Dec-18
Dec-18
Dec-18

This is a string field. I want to convert this string variable into the date format and needs to get two data items.

one is for the start date as like     Dec 01, 2018 12:00:00 PM  and
second is for the end date as like  Dec 31, 2018 12:00:00 PM.

Can you guide me how can I do it?


-------------------------------------------------

I got the logic.As of now the help not required :). Lynn already suggested to use
TO_DATE( '01-' || [Months], 'DD-MON-YY' ) which is working great.Thanks