Setting a listbox's value to a variable.

[#1]

Hi guys,

The question is probably a trivial one, I'm essentially looking for a good way to have a dynamic date as a bookmark.

Based on some reading I've done, I'm trying to set the value of the date to a variable that calculates the previous working day. However, I"m unsure of how to assign the value of the date list to the value of the variable itself. I'm hoping that it's possible for the user to click on the list annd press the '=' button and somehow access the variable. I've only started using the application this week so I'm still very ignorant when it comes to Qlikview in general.

I'd be grateful if someone could point me in the right direction.

Many thanks.

[swuehl]

I am not sure if I got your request correctly.

You are loading data with a Date field from a source, right?

First thing to assure is, that QV is interpreting this field correctly as containing date field values. Please check by opening the table viewer (CTRL-T) and hover over the field name with your mouse pointer. It should tell you some tag names, including $numeric and $date.

Then, you should be able to find the previous working day by using LastWorkDate() function in QV (please check the Help for the syntax, and note that you can pass a list of holidays, too). For example, enter in a text box:

=LastWorkDate(today(),2)

This should return you yesterday.

You can use this expression in a variable definition, so this variable will always return the previous working day.

If you create a listbox for field Date, and enter

=Date=LastWorkDate(today(),2)

into the search bar, it should select the prev. working date, too.

If you then create a bookmark, the bookmark will not store just the Date value, but the search expression for the field Date, so if you apply the bookmark in the future, the selected Date should still be correct.

Hope this helps,

Stefan

[#3]

Hi Stefan,

Thanks for the swift and informative response. Your understanding of my problem is 100% correct.

I think I understand how to store the value in the variable and I'm at a point where I can verify the correctness of the value by referencing it an an empty text box as an example.

The only difficulty I'm experiencing is using it in a listbox, nothing appears to be happening when I try using the expression in the search menu. I suspect it may have to do with the datatype itself as you mentioned in your first paragraph. When I check the tag for the day field which is the source for the listbox it only mentions $numeric and $integer which is probably the problem then?

Many thanks.

[swuehl]

The field tags should be ok, even when not $date is included.

Can you post the exact expression phrase your inputting in the list box search bar? And the exact name of your field?

Please note that QV is matching field names case sensitive.

[#5]

Ah okay, the name of the field is called Day.

So what I'm doing at the moment is clicking on the listbox(Day), opening the search bar and then typing the following:

=Day=LastWorkDate(today(),2)

[swuehl]

And Day field contains date values? Or Day of the month values [1..31]?

If you set an expression in your list box (Expressions tab),

=num(Day)

what do you see for e.g. a Day in October 2012?

edit:

If it is a date field, do you have values for all dates in the calendar? Or are some dates missing in your input data?

[#7]

The field looks like it contains Day of the month values. When setting the expression, I get back integer values (1-31).

I've just checked the source of the data for the dates and it looks like they are missing data, that would probably explain why it wasn't working then?

Thanks so much for the assistance, you've really helped a great deal!

[swuehl · Accepted solution]

Well,

LastWorkDate(today(),2)

is returning a date value, which is an integer (the number of days since Dec 30,1899).

I assumed you want to filter a date value in a list box, not a day of month values. These are two completely different concepts, i.e. the values are not compatible (edit: meaning, in a semantic way).

To make the values compatible, you need to apply day() function to a date to return the day of month value:

=Day=day(LastWorkDate(today(),2))

[Another edit:

If your source data is missing some data, e.g. the Day 10 (prev. working day), you won't be able to search and select this value, of course.

Consider using a so called master calendar, which contains all dates (and Days, Months, Years, etc.) in a given time range. Search the forum, there are plenty of posts and also some sample files available on how to create such a master calendar]

[#9]

Thanks Stefan, what you've said makes perfect sense. I'm more than happy to mark it as the answer since I'm sure my original question's been answered by now.

My listbox is still not finding the correct value though (even with a clean and full list of dates). I think the issue my issue may lie elsewhere however (for instance, when opening the list once I click on the enable expression checkbox and click OK, the next time I reopen it the checkbox is deselected). I suspect it may be that there's an issue with expressions being disabled for some reason.

[swuehl]

If you want, you can post a small sample app here to the forum (you can attach files in advanced editor).