Skip to Content
0

BusinessObjects 4.0 Using Dates For Comparison (If Then Else)

Oct 28, 2016 at 08:12 PM

1k

avatar image
Former Member

I have a date field that is in the format of DD-Mon-YYYY. How do I use that in a comparison? I want to populate a field on whether this date means a criteria.

For example, if I want to write =If[DateField]>31-Dec-1999.... what is the proper way to do that? I can't seem to find a way to include the date without the formula not validating.

10 |10000 characters needed characters left characters exceeded

Which tool are you using? Web Intelligence or?

0
Former Member

Sorry that I mised these replies. This is the first time I've used this new community format and I didn't get any email notifications of replies. I'll check what has been suggested. I'm actually designing the report in Webi, and this date issue has been nagging at me.

0
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Kuldeep Ghosh Oct 31, 2016 at 10:00 AM
0

If this is Webi, then you can use the below syntax:

=if(formatdate([Date Objects];"dd/MM/yyyy">"31/12/1999") then "Show" else "Hide"

Show 7 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Because of the date format of the field (see original post) I used the following:

=if(formatdate([Date Objects];"dd-Mmm-yyyy">"31-Dec-1999") then "Show" else "Hide"

but this gave me an error that the 'FormatDate' function was using an invalid data type. Can I not work with the date as it is currently displayed? I am not familiar with the FormatDate function.
0

There was a typo error in the formula i wrote above. A closing bracket was not placed ")". Try this:

=if(formatdate([Date Objects];"dd/MM/yyyy")>"31/12/1999") then "Show" else "Hide"

My first question would be, is the [date objects] returning the data in Date datatype or as a string.

With formatdate() you can convert a date field to string. You can then compare it to a string date. or the other way around to get the string "31/12/1999" converted to date. Both should work.

In your case i am guessing the [Date Object] is returning the date as a string because the input of formatdate() has to be date datatype. See the syntax below:

string FormatDate(date;format_string)

Verify what is being returned. If it is string, then use the below formula:

=if(formatdate(todate([Date Objects];"");"dd/MM/yyyy")>"31/12/1999") then "Show" else "Hide"

See if this helps.

1

Also I would suggest you to keep this link handy. This contains all the function list available in webi:

Webi Formula List

1
Former Member
Kuldeep Ghosh

The field is definitely a date field, confirmed by using the IsDate function on it. I'm not opposed to turning it into a string. Where I'm getting confused is that you are using a completely different date format than how the field is configured, and when I try to use the format of my actual date it returns an error.

My date is in the format

31-Dec-1999

and you are telling me to use

31/12/1999

How do make Webi see that those are equivalent?

0

Well it doesn't matter which format I use.It will always evaluate it depending on the way you define it. I can write the same thing in multiple ways. I just gave you an example:

=if(formatdate(todate([Date Objects];"");"dd/MM/yyyy")>"31/12/1999") then "Show" else "Hide"

or

=if(formatdate(todate([Date Objects];"");"dd-Mmm-yyyy")>"31-Dec-1999") then "Show" else "Hide"

or

=if(formatdate(todate([Date Objects];"");"dd/MM/yyyy hh:mm:ss")>"31/12/1999 00:00:00") then "Show" else "Hide"

or

=if(formatdate(todate([Date Objects];"");"dd/MM/yyyy hh:mm:ss")>"31/12/1999 00:00:00") then "Show" else "Hide"


Ideally the formula you need is (considering you are returning a date objects) is:

=if(formatdate([Date Objects];"dd-Mmm-yyyy") > "31-Dec-1999") then "Show" else "Hide"

1
Former Member
Kuldeep Ghosh

Interestingly, even though the date in my field is displayed as 31-Dec-1999 I used the formula


=if(formatdate([Date Objects];"dd/MM/yyyy") > "31/12/1999") then "Show" else "Hide"

and the logic evaluated correctly. I guess I understand better now that what FormatDate did was to convert whatever values were in my date field into a corresponding string based on the format I instructed. Very useful.

Offtopic: is there a way to have these new forums email you when you have replies?

0

I don't think that the email notification is introduced in this new version of community till now. @tammy.powlas3 can answer this i guess. Even I am trying to explore the new community space.

The best way to monitor it via Notification tab under your account.

1
AMIT KUMAR
Oct 31, 2016 at 01:48 PM
0

use this.

=if([Date Objects]>todate("31-Dec-1999";dd-Mmm-yyyy")) then "Show" else "Hide"

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

The field is already a date. When I use the IsDate function on the field it returns as True. Now I need to be able to use that date for a logical comparison but I can't figure out how to write the date in the comparison.

1
AMIT KUMAR
Oct 31, 2016 at 03:36 PM
0

what is the sample value in the date object?

date values format?

Share
10 |10000 characters needed characters left characters exceeded
AMIT KUMAR
Oct 31, 2016 at 03:38 PM
0

try like this

=if([Date Objects]>31/12/1999) then "Show" else "Hide"

based on the values in date object change the format of 31/12/1999

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

I tried to do that originally but when I try that as follows:

=if([Date Objects]>31-Dec-1999) then "Show" else "Hide"

I get a message that Dec is not recognized. Luckily the FormatDate function seems to be working with my date format.
0