Month and Year listbox selection

[#1]

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.  

[johnw]

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.

[#3]

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

[johnw]

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

[#5]

Thank you!

[#6]

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

;

[johnw]

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.

[#8]

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?

[johnw]

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.

[#10]

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