topic Help with Only function in QlikView

Help with Only function

mrchristopher — Wed, 14 Sep 2011 10:08:47 GMT

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

swuehl — Wed, 14 Sep 2011 10:33:07 GMT

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

Help with Only function

mrchristopher — Wed, 14 Sep 2011 10:58:20 GMT

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)