Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
I think you could also use
FieldValue('[Working Day]', ...
There is also a Lookup function
Lookup('[Working Day]', 'Date', Today(), 'DateTable')
Regards
Jonathan
However i believe lookup is a script level function
Regards
Rahul Lakhina
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
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
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?
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
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
= only(${< Date = {Today() } >} [Working Day] )