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.
,month([Term Date]) as TermMonth
,year([Term Date]) as TermYear
LOAD date(fieldvalue(['Term Date'],recno())) as [Term Date]
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.
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?
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.
(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?