Help with Only function

[mrchristopher]

I’m trying to write an expression similar to a vlookup in excel, however, I’m struggling as soon as I try to introduce a variable into the formula.

From the following data I want to pick out the CountofDays:

TrnMonth Workday CountofDays

9 01/09/2011 00:00 1

9 02/09/2011 00:00 2

9 05/09/2011 00:00 3

9 06/09/2011 00:00 4

9 07/09/2011 00:00 5

9 08/09/2011 00:00 6

9 09/09/2011 00:00 7

9 12/09/2011 00:00 8

9 13/09/2011 00:00 9

9 14/09/2011 00:00 10

9 15/09/2011 00:00 11

9 16/09/2011 00:00 12

9 19/09/2011 00:00 13

9 20/09/2011 00:00 14

9 21/09/2011 00:00 15

9 22/09/2011 00:00 16

9 23/09/2011 00:00 17

9 26/09/2011 00:00 18

9 27/09/2011 00:00 19

9 28/09/2011 00:00 20

9 29/09/2011 00:00 21

9 30/09/2011 00:00 22

Therefore to pick out today I use the following expression = Only({< Workday = {'14/09/2011 00:00:00'} >} CountofDays)

I now want to include the last invoice date to show the number of days done i.e. max(InvoiceDate), however, my best attempts are not working. Please can you help?

[swuehl · Accepted solution]

Hi mrchristopher,

I think you could use something like:

= Only({< Workday = {"=$(=max(InvoiceDate))"} >} CountofDays)

But you need to assure that InvoiceDate and Workdays are really dates to get the match, not timestamps.

(I think WorkDay is a date, just formatted with hours, right?)

Hope this helps,

Stefan

[mrchristopher]

Thanks Stefan, I've now wrapped the two date fields with the Date function in the script and with a little bit of editing I got the expression working:

= Only({< Workday = {"$(=max([InvoiceDate]))"} >} CountofDays)