Skip to Content
0

How to construct a Variable relating to Context

Jan 17, 2017 at 08:15 PM

27

avatar image

Hi

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

Will

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

AMIT KUMAR
Jan 17, 2017 at 08:30 PM
0

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])

Share
10 |10000 characters needed characters left characters exceeded