10 Replies Latest reply: Feb 21, 2012 3:26 PM by John Witherspoon RSS

    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.  

        • Re: Month and Year listbox selection
          John Witherspoon

          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

            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
                John Witherspoon

                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

                    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
                        John Witherspoon

                        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

                            (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
                                John Witherspoon

                                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:

                                 

                                ID

                                Term Date

                                Term Month

                                Term Year

                                 

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