Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Month and Year listbox selection

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.  

10 Replies
MVP
MVP

Re: Month and Year listbox selection

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.

Not applicable

Month and Year listbox selection

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..

MVP
MVP

Re: Month and Year listbox selection

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

Not applicable

Month and Year listbox selection

Thank you!

Not applicable

Month and Year listbox selection

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'])

;

MVP
MVP

Month and Year listbox selection

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.

Highlighted
Not applicable

Month and Year listbox selection

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?

MVP
MVP

Re: Month and Year listbox selection

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.

Not applicable

Re: Month and Year listbox selection

(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?