15 Replies Latest reply: Sep 3, 2009 11:26 PM by John Witherspoon RSS

    Display months in a listbox

    Penelope

      Hi,

      I've got this excel sheet among other things a column with payment dates. I want to use this info in a listbox so that I can see sales figures for a certain month in a diagram.

      I've figured out how to get just the month in a text format (jan, feb, etc) but the problem is I get every instance of the months displayed, so there is a whole lot of jans, febs etc seen where as I just want one instance of each so that I can choose for example may and then get the sales total for the month of may.

      I don't know if I explained this well enough, but hoping that I have, is there anyone out there who can give me any tips?

      Thanks!

       

        • Display months in a listbox

          When loading the script:

          Sometimes its best to create a seperate Date_Table and then link this to (for example) the orders table.

          By using this it allows the developer to manipulate dates easily

          Date:

          load

          today()-recno()+1 as Date

          autogenerate(today()-'2002-12-31');

          DateParts:

          load

          Date as invoice_date, // link to orders_table

           

          Day(Date) as Day,

          Year(Date) as Year,

          yearname ( Date, 0, 4 ) as FinancialYear,

          weekstart(Date) as WeekStart,

          Month(Date)&' - '&right(year(Date),2) as MonthYear,

          Month(Date) as Month,

          Week(Date) as Week,

          Weekday(Date) as WeekDay

          resident Date;

          drop table Date;

          Then when you create list boxes of dates or date format the duplication will not occur.

           

            • Display months in a listbox
              Penelope

              I am pretty much a beginner to QlikView. I've tried the code above in the script window but I get an syntax error message saying there's a missing/misplaced load:from statement and I have no idea what that means.

              Could you perhaps add a few more comments to the code? I'm guessing there is no way to just manipulate the listbox properties to achieve this?

                • Display months in a listbox
                  John Witherspoon

                  It's probably a field definition problem, not a listbox problem. You could manipulate the listbox, but that's not really the right solution.

                  My guess is that you're doing something like this:

                  date(MyDate,'MMM') as Month

                  The problem with that is that all dates are still stored as dates, regardless of how you display it. The 'MMM' is only telling it how you want to see it on the screen. So September 1, 2008 will still be different than September 2, 2008 will still be different than September 2, 2009. As a result, you'll see Sep repeated in your list box for every date in September of any year.

                  If you want all dates in September of any year to be equivalent, use this:

                  month(MyDate) as Month

                  The month() function drops the date information, and stores only an integer month (1-12) with a display format of MMM.

                  If you want all dates in September of EACH year to be equivalent, but different from September in other years, use this:

                  date(monthstart(MyDate),'MMM YY') as Month

                  The monthstart() function forces all dates in the month to have the same value.

                    • Display months in a listbox
                      Penelope

                      At the risk of seeming completely illiterate, I must admit I can't get it to work.

                      In the excel sheet I'm importing there is in addtion to the column "invoice date" also a column called "Month" which is derived from Invoice date, but of course lists only the month in a MMM-format.

                      So, is it the wrong way to go about it to import that column and try to get that into a listbox which only has one instance of every month displayed? Should I, delete this column from the sheet and extract the month-bit from the invoice date-column? I really need all the help I can get as I'm stuck and this is part of an assignment I have to hand in at the latest tomorrow.

                       

                        • Display months in a listbox

                          Can you load the document and I can have a look at the script!

                            • Display months in a listbox
                              Penelope

                               

                              I hope I understood you correctly. I loaded the file into QV and below is the copied script. The column invoice date corresponds to UCIVDT. If I have completely misunderstood you, then my excuse is that after an all-nighter in front of the computer my brain is mush.

                               

                               

                              UCCUNO,

                              UCIVNO

                               

                               

                              ,

                              UCIVDT

                               

                               

                              ,

                              UCORNO

                               

                               

                              ,

                              UCITNO

                               

                               

                              ,

                              UCORTP

                               

                               

                              ,

                              UCSMCD

                               

                               

                              ,

                              UCWHLO

                               

                               

                              ,

                              UCSAAM

                               

                               

                              ,

                              UCUCOS

                               

                               

                              ,

                              UCIVQT

                               

                               

                              ,

                              UCDIVI

                               

                               

                              ,

                              UCCONO

                               

                               

                              ,

                              MONTH

                               

                               

                              ,

                              YEAR

                              FROM

                              [C:\Documents and Settings\HP_Ägaren\Mina dokument\QlikTech\Rekrytering_Uppgift_EmbLic\Rekrytering_Uppgift\test\OSBSTD.xls]

                              (

                               

                              biff, embedded labels, table is Sheet1$)

                              ;





                               

                               

                               

                              LOAD





                    • Display months in a listbox

                      Can you upload the document.

                      The instructions are in the attached file

                        • Display months in a listbox
                          Penelope

                          Ok, am with you now. Here's the excelfile.

                            • Display months in a listbox

                              Sorry,

                               

                              I meant the qvw (qlikview) file

                                • Display months in a listbox
                                  Penelope

                                  Ah...ok, well here it is. It's a work in progress...

                                  I have to step put for a while but will check back in a couple of hours. Thanks for the help

                                    • Display months in a listbox

                                      Document Uploaded!

                                      Can you refresh the script when you open and save the file back to its original location. Notice in the script I have created a new tab called Dates (as discussed earlier).

                                       

                                      Also hit Ctrl T and you can see the table structure

                                       

                                        • Display months in a listbox
                                          Penelope

                                          Thank you for the help, alas QlikView won't let me open the file. I suspect it's because I have the so called personal edition that I downloaded from the site.

                                            • Display months in a listbox

                                              // Copy and paste the following script and then reload the document.

                                              //Then create a list box using the dimension My_Month

                                               

                                              //To look at the table structure after the reload hit Ctrl T



                                              LOAD UCCUNO,

                                              UCIVNO,

                                              UCIVDT,

                                              UCORNO,

                                              UCITNO,

                                              UCORTP,

                                              UCSMCD,

                                              UCWHLO,

                                              UCSAAM,

                                              UCUCOS,

                                              UCIVQT,

                                              UCDIVI,

                                              UCCONO,

                                              MONTH &' - '& YEAR as MonthYear,

                                              MONTH,

                                              YEAR

                                              FROM

                                              [C:\Documents and Settings\HP_Ägaren\Mina dokument\QlikTech\Rekrytering_Uppgift_EmbLic\Rekrytering_Uppgift\test\OSBSTD.xls]

                                              (biff, embedded labels, table is Sheet1$);

                                               

                                              LOAD OKDIVI,

                                              OKCONO,

                                              OKCUNO,

                                              OKCUNM,

                                              OKCSCD,

                                              OKCUCL,

                                              OKSDST,

                                              OKCUA1,

                                              OKCUA2,

                                              OKSTAT,

                                              OKCUA3,

                                              OKCUA4,

                                              OKECAR,

                                              OKPHNO,

                                              OKPONO,

                                              OKTEPY,

                                              OKTBLG, // No Date Column

                                              OKTOIN,

                                              OKTDIN

                                              FROM

                                              [C:\Documents and Settings\HP_Ägaren\Mina dokument\QlikTech\Rekrytering_Uppgift_EmbLic\Rekrytering_Uppgift\test\ocusma.xls]

                                              (biff, embedded labels, table is Sheet1$);

                                              LOAD UCCUNO,

                                              UCIVNO,

                                              UCIVDT,

                                              UCORNO,

                                              UCITNO,

                                              UCORTP,

                                              UCSMCD,

                                              UCWHLO,

                                              UCSAAM,

                                              F10,

                                              UCIVQT,

                                              UCDIVI,

                                              UCCONO,

                                              MONTH &' - '& YEAR as MonthYear,

                                              MONTH,

                                              YEAR

                                              FROM

                                              [C:\Documents and Settings\HP_Ägaren\Mina dokument\QlikTech\Rekrytering_Uppgift_EmbLic\Rekrytering_Uppgift\test\OSBSTD.xls]

                                              (biff, embedded labels, table is Sheet1$);

                                              LOAD UCCUNO,

                                              UCIVNO,

                                              UCIVDT,

                                              UCORNO,

                                              UCITNO,

                                              UCORTP,

                                              UCSMCD,

                                              UCWHLO,

                                              UCSAAM,

                                              F10,

                                              UCIVQT,

                                              UCDIVI,

                                              UCCONO,

                                              MONTH &' - '& YEAR as MonthYear,

                                              MONTH,

                                              YEAR

                                              FROM

                                              [C:\Documents and Settings\HP_Ägaren\Mina dokument\QlikTech\Rekrytering_Uppgift_EmbLic\Rekrytering_Uppgift\test\OSBSTD.xls]

                                              (biff, embedded labels, table is Sheet1$);

                                              SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

                                              SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';

                                              SET DayNames='må;ti;on;to;fr;lö;sö';

                                              LOAD UCCUNO,

                                              UCIVNO,

                                              UCIVDT,

                                              UCORNO,

                                              UCITNO,

                                              UCORTP,

                                              UCSMCD,

                                              UCWHLO,

                                              UCSAAM,

                                              UCUCOS,

                                              UCIVQT,

                                              UCDIVI,

                                              UCCONO,

                                              MONTH &' - '& YEAR as MonthYear,

                                              MONTH,

                                              YEAR

                                              FROM

                                              [C:\Documents and Settings\HP_Ägaren\Mina dokument\QlikTech\Rekrytering_Uppgift_EmbLic\Rekrytering_Uppgift\test\OSBSTD.xls]

                                              (biff, embedded labels, table is Sheet1$);

                                              LOAD UCCUNO,

                                              UCIVNO,

                                              UCIVDT,

                                              UCORNO,

                                              UCITNO,

                                              UCORTP,

                                              UCSMCD,

                                              UCWHLO,

                                              UCSAAM,

                                              UCUCOS,

                                              UCIVQT,

                                              UCDIVI,

                                              UCCONO,

                                              MONTH &' - '& YEAR as MonthYear,

                                              MONTH,

                                              YEAR

                                              FROM

                                              [C:\Documents and Settings\HP_Ägaren\Mina dokument\QlikTech\Rekrytering_Uppgift_EmbLic\Rekrytering_Uppgift\OSBSTD.xls]

                                              (biff, embedded labels, table is Sheet1$);

                                               

                                              Use this script to create the following table

                                              Date:

                                              NOCONCATENATE load

                                              today()-recno()+1 as Date

                                              autogenerate(today()-'2002-12-31');

                                              DateParts:

                                              load

                                              Date as invoice_date,

                                               

                                              Day(Date) as Day,

                                              Year(Date) as Year,

                                              yearname ( Date, 0, 4 ) as FinancialYear,

                                              weekstart(Date) as WeekStart,

                                              Month(Date)&' - '&right(year(Date),2) as MonthYear, // link to Table1

                                              Month(Date) as My_Month,

                                              Week(Date) as Week,

                                              Weekday(Date) as WeekDay

                                              resident Date;

                                              drop table Date;

                                               

                                                • Display months in a listbox
                                                  Penelope

                                                  I get it to work so far that I get the listbox but when I add it and try to use it in a chart, 'No data to display' is shown

                                                  What am I doing wrong?

                                                  Again, thanx for your help and above all, patience

                                                    • Display months in a listbox
                                                      John Witherspoon

                                                      It looks like you've moved beyond this now, but to answer an old question more directly, I would not load the month field from the Excel sheet. I would rederive it directly from the invoice date. And generally speaking, month would go in a separate table, which is what carneyfm is trying to set up for you. So I guess I'm just adding my two cents to say I think you're on the right track.