Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mrchristopher
New Contributor III

Help with Only function

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?

1 Solution

Accepted Solutions
MVP
MVP

Help with Only function

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

2 Replies
MVP
MVP

Help with Only function

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
New Contributor III

Help with Only function

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)