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.