vlookup style calculation required

[#1]

Hi,

am trying to get a specfic value to appear in a text object.  My data is as follows

Date,  Working Day, Total Work Days

2/7/12,     1,     22

3/7/12,     2,     22

4/7/12,     3,     22

5/7/12,     4...

and so it continues to the end of the month giving the working day.

I don't have a problem displaying the total working days i.e. 22; however how do I display today's working day?  (If I was using Excel it would simply be =(VLOOKUP(today(),A1:Cxx,2.FALSE).)  Is it possible to do something similar in my Qlikview doc?

Thanks in advance.

C

[#2]

Dear C,

Try using the following syntax:

FieldValue('Working_Day',FieldIndex('Date',Today()))

Note the change in field name, it seems you would have to change the mane to make it work.

Regards

Rahul

[jonathandienst]

I think you could also use

FieldValue('[Working Day]', ...

There is also a Lookup function

Lookup('[Working Day]', 'Date', Today(), 'DateTable')

Regards

Jonathan

[#4]

However i believe  lookup is a script level function

Regards

Rahul Lakhina

[#5]

Hi Rahul,

Thank you for that.  Within the parameters of my simple example that seemed to work fine; however whe I tried to apply it to the actual document I'm working on it fails.  It simply return a "-". I've tried various varibles and variations to no avail.  I attach a sample of what I have with your suggestion, could I ask you to point out what it is I'm doing wrong?

Thank you for your advice in advance.

C

[jonathandienst]

Hi

The fieldindex/fieldvalue aproach only works if you use the same field (or in some cases fields that are loaded simultaneously with the same cardinality and a 1:1 correspondence). Your workday and calendardate fields do not meet these requirements.

However, the two fields are associated, and you can see that working in your table box. For a straight table, if CalendarDate is one of the dimensions, just use =WorkDay. The associations will take care of the lookup for you.

I hope that I understood your requirements correctly. See the attached for a straight table chart as I described.

Regards

Jonathan

[#7]

Hi Jonathan,

Thank you for your response and I see what you mean with regards to the one-to-one relationship.

My requirement is simply to have what the working day is 'today' so that I can then use that value in a calculation for average daily sales for the current month to date. i.e. I've made £1m from the 1/7/2012 to-date so that would be £1m/6 working days. I don't actually want the user to be selecting todays date.  Does that make sense?

[jonathandienst]

Perhaps something like

=only({<Date = {"$(=Today())"}>} WorkingDay)

will return today's working day number. Check that it works, and you can then use that expression as the divisor for your average.

Regards

Jonathan

[#9]

Jonathan,

Thank you for the suggestion however it didn't appear to provide me a result.

I have actually found a work around for the purpose of my document which relates back to your suggestion of using lookup.  I am declaring a variable at the script level - LET varWorkDay = Lookup('WorkDay', 'Cal_Date', today(), 'DateTable').

Thanks for the advice.

C

[#10]

= only(${< Date = {Today() } >} [Working Day] )