topic Re: Setting a listbox's value to a variable. in QlikView

Setting a listbox's value to a variable.

Thu, 11 Oct 2012 08:33:02 GMT

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.

Re: Setting a listbox's value to a variable.

swuehl — Thu, 11 Oct 2012 09:24:01 GMT

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

Re: Setting a listbox's value to a variable.

Thu, 11 Oct 2012 11:16:14 GMT

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.

Re: Setting a listbox's value to a variable.

swuehl — Thu, 11 Oct 2012 11:59:02 GMT

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.

Re: Setting a listbox's value to a variable.

Thu, 11 Oct 2012 12:04:52 GMT

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)

Re: Setting a listbox's value to a variable.

swuehl — Thu, 11 Oct 2012 12:09:27 GMT

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?

Re: Setting a listbox's value to a variable.

Thu, 11 Oct 2012 12:29:15 GMT

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!

Re: Setting a listbox's value to a variable.

swuehl — Thu, 11 Oct 2012 12:43:59 GMT

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]

Re: Setting a listbox's value to a variable.

Thu, 11 Oct 2012 14:01:36 GMT

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.

Re: Setting a listbox's value to a variable.

swuehl — Thu, 11 Oct 2012 14:05:40 GMT

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

Re: Setting a listbox's value to a variable.

Thu, 11 Oct 2012 14:11:29 GMT

Thanks Stefan, here's a quick sample file containing a single list.

If you'd be able to take quick look and get the expression working in the list it'd be much appreciated.

Re: Setting a listbox's value to a variable.

swuehl — Thu, 11 Oct 2012 15:45:54 GMT

First of all, I need to correct the above expressions:

You need to use FirstWorkDate() function, not LastWorkDate().

I inputted LastWorkDate(today(),2) in a text box and got Oct-10 back, the correct result, and I haven't noticed that I am using a qvw I opened two days ago. today() will return the date when the qvw has been opened (check the different timer argument settings in the Help, if needed).

Then, if you select values in a field, and then perform a search, I think it will search only in the selected, i.e. possible range of values by default.

When I have opened your sample, I' ve seen that you selected 13 in Day field. So a simple search expression will not work, because it will only see one possible value. You'll need to clear the selection first or use

=only({1}Day)=day(FirstWorkDate(today(),2))

Then 10 will show up when typing this expression and will be selected after submitting the search. The only({1} Day) will expand the search to all Day values, not only to the selected ones.

Hope this helps,

Stefan

Re: Setting a listbox's value to a variable.

Fri, 12 Oct 2012 07:14:17 GMT

Thanks Stefan, that seemed to work perfectly. I had no idea that selecting a value initially would make a difference with the expression. The pointers and help were immensely beneficial

Re: Setting a listbox's value to a variable.

swuehl — Fri, 12 Oct 2012 08:44:10 GMT

Unfortunately the search capabilities are one of the least documented areas in QV.

When I said, that search will be limited to the current selection, I was talking about this search format:

=FIELDNAME=SOMEEXPRESSION

For example, is you select 13 and want to search 2, you need to write

=only({1}Day)=2

But for searching literals, it's easier to just put in 2 in the search box, and this method will find also the values that are not possible in your current selection. Kind of confusing sometimes...

Re: Setting a listbox's value to a variable.

Wed, 22 Jan 2014 19:41:50 GMT

Hi Stefan,

I could use some help on this too! My company is addicted to using "Year-Week" (e.g. 2013-12, 2014-01) as a time period, even though it's text and is a pain to sort.

I created a numerical version using

(Year(ActivityDate)&if(Num(Week(ActivityDate))<=9,'0'&Num(Week(ActivityDate)),Num(Week(ActivityDate)))) As KYW

But, when I go to the listbox, and enter in the search box '=KYW=MAX(KYW)', it returns all weeks, not the last one.

Any suggestions?