I need some help with creating a variable.
I have a report that shows a list of employees who have been absent due to sickness.
I created a variable that shows the maximum date representing the start date of the medical certificate. In other words this just shows me the most recent start date of the medical certificate. This is based on a field called Cert Type being populated with the phrase "Doctor's Certificate"
This all works fine.
I then added in a new dimension to show the Reason for the Certificate. Not all Certifcates have this field populated.
I am now getting more than 1 record for some employees. I only want to see the Latest Dr Cert Start Date if the Cert Type = Doctor's Certificate. Also, if the Cert Reason field is populated for this record I also want it to show. If the Cert Reason field is blank then as long as the Cert Type = Doctor's Certificate I want to show the Latest Dr Cert Start Date.
Can anyone suggest a solution?