2 Replies Latest reply: Sep 14, 2011 6:58 AM by Chris Draper RSS

    Help with Only function

    Chris Draper

       

      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?

       

       

       

        • Help with Only function
          Stefan Wühl

          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