Date comparison

[former_member647411]

I have 4 date columns, let's say, D1, D2, D3 and D4. There can be null values in any or all of them. I need to get the latest / earliest date from this four in a separate column. And that is the only column which will be displayed in the original report. How do I do that ?

[Former Member · Accepted solution]

Hi Asad Zaman:

Create variables named [D1a] with formula =If(IsNull([D1])) Then 0 Else [D1]

similarly create variables named [D2a], [D3a], and [D4a] with similar formula as above.

Created a variable named [Latest] with formula

=If([D4a]>[D1a]) Then (If([D4a]>[D2a]) Then (If([D4a]>[D3a]) Then [D4a] Else [D3a]) ElseIf

([D2a]>[D3a]) Then [D2a] Else [D3a]) ElseIf ([D1a]>[D2a]) Then (If([D1a]>[D3a]) Then [D1a]

Else [D3a]) ElseIf([D2a]>[D3a]) Then [D2a] Else [D3a]

[Latest] gave me the latest among the 4 dates and could stand alone along with my dimension in the report.

Created variables named [D1b] with formula =If(IsNull([D1])) Then CurrentDate() Else [D1]

similarly create variables named [D2b], [D3b], and [D4b] with similar formula as above.

Created a variable named [Earliest or Today] with formula

=If([D4b]<[D1b]) Then (If([D4b]<[D2b]) Then (If([D4b]<[D3b]) Then [D4b] Else [D3b]) ElseIf ([D2b]<[D3b]) Then [D2b] Else [D3b]) ElseIf ([D1b]<[D2b]) Then (If([D1b]<[D3b]) Then [D1b] Else [D3b]) ElseIf([D2b]<[D3b]) Then [D2b] Else [D3b]

Created a variable named [Earliest] with formula =If([Earliest or Today]<>CurrentDate()) Then [Earliest or Today] elseif([D1]=currentdate() or [D2]=currentdate() or [D3]=currentdate() or [D4]=currentdate()) then currentdate()

[Earliest] gave me the earliest among the 4 dates and could stand alone along with my dimensions in the report.

This Report will need to be "Refreshed on Open" else you will get today's date as the earliest if there are no values in all 4 dates.  Refreshing the report manually would also remove Today's date being displayed as the least when there are no values for all 4 dates.

Depending on your webi version and patch, you might get the result as one day previous.  Please refer here http://scn.sap.com/message/7443734

Find the results below.

Regards,

Santhosh Kumar Prabhakaran

Message was edited by: Santhosh Kumar Prabhakaran

Corrected [Earliest] to display today's date if that is the lowest value. Reworded the reply for better understanding and to suit the user's nomenclature. Message was edited by: Santhosh Kumar Prabhakaran

[former_member647411 · Accepted solution]

Santhosh,

Really hard work. Appreciate it. BTW, Earliest date and latest dates will be displayed in two different reports. You did not clarify if all four dates are null what will happen ? Also, you did not consider, if two dates are equal, for example, D1=D2. Why do I have to consider current date ? Four independent date objects are coming from universe. I just need to get the earliest date in one report and the latest date in another report. There can be one or more null values, there can be same dates.

But I really appreciate your elaboration. Beyond my expectation.

[manesh_ks · Accepted solution]

Hi Azad,

In backend if you can create a table with primary key and then 4 dates

Primary Key      Date

P1                    D1

P1                    D2

P1                    D3

P1                    D4

P2                    D1

P2                    D2

.

.

.

.

Now if you run the query for Max(Date) and grouping primary key .. it will show only latest date.

Thanks

Manesh

[Former Member · Accepted solution]

Hi Asad Zaman:

Thanks for the recognition.  It did take some effort .

If you look at Task T6 in the image attached in the earlier reply, it returns 0 for latest date and no value for the earliest date.  That is my entry with all 4 dates blank.

I did not cover all 4 being equal.  To achieve that simply change the ">" and "<" to ">=" and "<=" respectively.  Find modified [Latest] formula below

=If([D4a]>=[D1a]) Then (If([D4a]>=[D2a]) Then (If([D4a]>=[D3a]) Then [D4a] Else [D3a]) ElseIf

([D2a]>=[D3a]) Then [D2a] Else [D3a]) ElseIf ([D1a]>=[D2a]) Then (If([D1a]>=[D3a]) Then [D1a]

Else [D3a]) ElseIf([D2a]>=[D3a]) Then [D2a] Else [D3a]

Modified [Earliest or Today] formula

=If([D4b]<=[D1b]) Then (If([D4b]<=[D2b]) Then (If([D4b]<=[D3b]) Then [D4b] Else [D3b]) ElseIf ([D2b]<=[D3b]) Then [D2b] Else [D3b]) ElseIf ([D1b]<=[D2b]) Then (If([D1b]<=[D3b]) Then [D1b] Else [D3b]) ElseIf([D2b]<=[D3b]) Then [D2b] Else [D3b]

This should take care of the issue.

[valluvan_navalan]

Hi Asad Zaman,

Could you please elaborate  your current requirement? As per my understanding top of your question (D1,D2,D3,D4) date comparison if null then you are going to populate in single column right?

My question is if D2 and D4 columns contain null then which column is considered ? or we need to compare both columns which is latest date right ?

Regards,

Valluvan N

[former_member647411]

If all are null, then it should be null. If two dates are null then, it should compare the remaining two and take the latest/earliest. If three are null, it will just populate the available one. So, it should compare based on available non null value. If four dates are available, it should take only the earliest/latest one.

[former_member647411]

Can anyone help me on this ?

[former_member188911]

Hi Asad,

nobody so far understood what you need to do, if you can please expound on this you will probably get help

Thanks

SImone

[Former Member]

Hello Asad,

Can you give an example of your requirement?

Thanks,

Vivek