topic Re: Month and Year listbox selection in QlikView

Month and Year listbox selection

Thu, 17 Nov 2011 19:55:07 GMT

Hi,

This seems like a basic question as this feature seems to be common. I've seen this accross the top of most example dashboards.

How can I take a sales date column and create a listbox that contains unique values of the dates in both month spelled out (Jan) format and YYYY.

Also, how do you make the listbox horizontal instead of vertical?

Thanks.

Re: Month and Year listbox selection

johnw — Thu, 17 Nov 2011 20:00:49 GMT

In script, something like this to generate a calendar table based on your sales dates. The calendar table will add the month and year fields to your model.

[Calendar]:
LOAD *
,month(OrderDate) as OrderMonth
,year(OrderDate) as OrderYear
;
LOAD date(fieldvalue('OrderDate',recno())) as OrderDate
AUTOGENERATE fieldvaluecount('OrderDate')
;

To make the list box horizontal, in the properties, presentation tab, uncheck "single column" and then make the box short and wide.

Month and Year listbox selection

Thu, 17 Nov 2011 20:10:00 GMT

Thanks John!

It came back with this error.

--------------------------------------

Error in expression:
FieldValue takes 2 parameters

LOAD date(fieldvalue('OrderDate'),recno())) as "Sales Date"
AUTOGENERATE fieldvaluecount('OrderDate')

--------------------------------------

OrderDate has my dates in it..

Re: Month and Year listbox selection

johnw — Thu, 17 Nov 2011 20:28:20 GMT

Whoops, I put an extra right paren in there. Should be this I think, and I'm correcting the previous post.

LOAD date(fieldvalue('OrderDate',recno())) as OrderDate

Month and Year listbox selection

Thu, 17 Nov 2011 20:49:55 GMT

Thank you!

Month and Year listbox selection

Sat, 18 Feb 2012 01:26:58 GMT

I too have the same problem and tried to used the script provided. I have a termination data on a page and my source file has 1/1/2010 for Example - I need to be able to select any month in a list box and then and year in a list box and I of course do not want to manipulate the source data. Is the formula I am using correct? Term Date is field heading.

[Calendar]:

LOAD *

,month([Term Date]) as TermMonth

,year([Term Date]) as TermYear

;

LOAD date(fieldvalue(['Term Date'],recno())) as [Term Date]

AUTOGENERATE fieldvaluecount(['Term Date'])

;

Month and Year listbox selection

johnw — Tue, 21 Feb 2012 17:40:03 GMT

The script given assumes you've already loaded "Term Date" from your source, and that it's a QlikView date. I'm not clear if you've done that. You're also not matching the syntax for the fieldvalue() and fieldvaluecount() functions, which don't use brackets.

Month and Year listbox selection

Tue, 21 Feb 2012 18:51:28 GMT

Yes John, Term Date is a field in my source document. I'm not sure I understand what you mean when you say 'if it's a QlikView date' When I read it makes me think I will have to edit my source data which I don't want to do. Am i thinking about this correctly?

In terms of the syntax, i'm purely a QV novice - what am I doing wrong there?

Re: Month and Year listbox selection

johnw — Tue, 21 Feb 2012 19:07:33 GMT

A QlikView date means a date in QlikView's internal format, often created using the date() and/or date#() functions.

No, don't edit the source data. I gather that your source document is an Excel file. I don't use many Excel files, but it looks like QlikView understands an Excel date without any further manipulation. So your script might look something like this:

[Data]:
LOAD
ID
,[Term Date]
FROM TermDate.xlsx (ooxml, embedded labels, table is Sheet1)
;
[Calendar]:
LOAD *
,month([Term Date]) as [Term Month]
,year([Term Date]) as [Term Year]
;
LOAD date(fieldvalue('Term Date',recno())) as [Term Date]
AUTOGENERATE fieldvaluecount('Term Date')
;

I personally prefer to have my Month fields include the year, and both my Month and Year fields to be QlikView dates (so that I can manipulate them with QlikView date functions). That would look like this instead:

[Data]:
LOAD
ID
,[Term Date]
FROM TermDate.xlsx (ooxml, embedded labels, table is Sheet1)
;
[Calendar]:
LOAD *
,date(monthstart([Term Date]),'MMM YYYY') as [Term Month]
,date(yearstart([Term Date]),'YYYY') as [Term Year]
;
LOAD date(fieldvalue('Term Date',recno())) as [Term Date]
AUTOGENERATE fieldvaluecount('Term Date')
;

For syntax questions, or other questions about how functions are used, I'd recommend just searching for them in the help file. That should give you syntax, how the function is used, and typically a few examples.

Re: Month and Year listbox selection

Tue, 21 Feb 2012 20:20:39 GMT

(i have not tried it) but this is great John, You are correct, I am working from an excel file, so then when I go to create the list box - what field am I choosing to be the selection? is it monthstart (for months)?

And I agree I like to have years associated with months - Does your formula above capture that?

Re: Month and Year listbox selection

johnw — Tue, 21 Feb 2012 20:26:41 GMT

The field names are the part in [] after "as" in each of the loads, or just the name given in the source file if there is no "as". You'll see them when you build a list box, so once you've run the script, it shouldn't be confusing. They'll be these for the above example:

Term Date

Term Month

Term Year

The second script I gave includes the years associated with the months. The first does not.