If you are unable to create a new account, please email support@bspsoftware.com

 

Getting the bigger date (Max Date) Between two dates based on Condition

Started by HelloCognos, 23 Mar 2018 12:44:25 PM

Previous topic - Next topic

HelloCognos

Hi,
I have to get the maximum date between two different data elements so I can use the data item in a list. May be using a Case Statement of if and else.

So

Unique ID        Condition 1                 Date 1                Date 2             Date 3 (Data item)

111                 ABC                           01/01/2017        09/01/2017      09/01/2017
112                HGH                           02/02/2018        03/03/2017       03/03/2017

                     
If Date 2 is bigger than date 1, take date 2 values. If Date 1 is bigger than Date 2, take Date 1

So the Date 3 as the Data Item will include the latest date for ABC and HIGH conditions.
Thanks

HelloCognos

Here is what I'm trying to do but getting error: when type of Bill is paper, get the max between the dates for Paper and Electronics.

case
when [Type Of Bill] in ('Paper') and [Settled Date] >= '2015-04-17' then
maximum ([Settled Date] for [Type Of Bill]  in ('Paper','Electronics')
else
[Settled Date]
end

Thanks a lot@ :) :)