Skip to Content
1
May 11, 2020 at 04:24 PM

Business Objects - If And Statement Choose difference between earliest date and run date

693 Views Last edit May 11, 2020 at 04:39 PM 2 rev

I am trying to write a variable in Business Objects.
**please note I have made up the field names and situation as data I am working with is confidential.

Basically if name is Joe or name is Jill and surname is Doe then how many days since they last visited the chemist.

=If ((name] = “Joe” or ([name] = “Jill” and ([surname]= “Doe”)) Then([Days since last chemist visit])

So I need to figure out how to write the variable named Days Since last Chemist visit. Joe or Jill have had several visits to the chemist and so I need the variable to pick the newest date (I.e the last date they visited the chemist) and find the difference between that date and run date.

I have been using the following variable

Days since last chemist visit variable:

=daysbetween([date of visit]);LastExecutionDate())


however because I see a customer detailed view in business objects and I need to e.g. customer name, customer surname, customer iD, customer visit date then this variable is only working out the difference between the visit date and the run date but I need it to take the newest visit date and show the difference of the newest visit date to the run date. therefore if Joe Doe is in the table 3 times with 3 different visit dates the days since chemist visit should all show the same amount of days.

E.g

Name: Joe Doe, Visit Date: 20/04/2020, Days since last chemist visit: 21 days

Name: Joe Doe, Visit Date: 01/04/2020, Days since last chemist visit: 21 days

Name: Joe Doe, Visit Date: 15/02/2020, Days since last chemist visit:21 days


as you see the example above the variable should be showing 21 days against the customer each time.