Display months in a listbox

[#1]

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!

[#2]

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

[#3]

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

[#4]

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.

[#5]

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

(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

(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

(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

(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

(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

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

[#6]

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

[johnw]

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.