Skip to Content

How to construct a Variable relating to Context


I am trying to find a way to show the Reason code in relation to the Start Date: Absence Cert depending on the Perf Ref number. In my report I have 3 columns as shown below.

The Reasons (although not displayed here) are as per the dates so 14/9/16 is Hip Pain and so on.

I want to be able to only show the most recent Start Date:Absence Cert e.g. 02/01/17 for each Perf Ref number, so I need a variable that can do this.(Max??)

The I need to show the Reason that is relevant to the most recent date for that person.

In this case it would be 02/01/17 and the reason would show as Flu.

Can anyone suggest how to write a variable to do that?

Pers Ref e.g.4001219

Start Date:Absence Cert 14/9/16, 26/9/16, 21/11/16, 02/01/17

Reason. Hip Pain, Hip Replacement, Post Op, Flu

Many thanks


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 17, 2017 at 08:30 PM

    you can achieve this by multiple ways.

    option1:create variable Show/Hide=If([Start Date]=Max([Start Date]) In ([Pers Ref])) Then 1 Else 0

    Apply table filter on Show/Hide where equal to 1

    option 2:

    in the table all three column.

    For Start date=Max([Start Date])

    Reason=Max([Reason]) Where (Max([Start Date]) In ([Pers Ref])=[Start Date])

    Add comment
    10|10000 characters needed characters exceeded