topic Change date field in QlikView

Change date field

Wed, 23 Sep 2009 21:49:19 GMT

My database contains many date fields like:

01-01-2009
01-02-2009
01-03-2009
01-03-2009

02-01-2009
02-02-2009
etcetera

I would like Qlikview to give a selection by month instead of days. Changing numberfield to date won't help? So can Qlikview sumarise 01-01-2009 to januari 2009 etc ?

Change date field

Wed, 23 Sep 2009 22:10:27 GMT

=monthname(makedate(right(datefield,4),left(datefield,2), mid(datefield,4,2)))

Change date field

prieper — Wed, 23 Sep 2009 22:12:17 GMT

Use the date-functions, e.g. your script may look like


LOAD *, MonthName(Date) AS Month;
LOAD Date#(YourDate, 'MM-DD-YYYY') AS Date FROM .....;

HTH
Peter

Change date field

Thu, 24 Sep 2009 15:09:09 GMT

I've used the date-function you mentioned. Now I can select months but the link does not work.

LOAD

Group, Name, MSISDN, Date, Costs

FROM

(

txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD

*, MonthName(Date) AS Month;

LOAD

Date#(Date, 'MM.DD.YYYY') AS Date

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

----------------------------------

The link between the 2 tables now exists (my table and the date table). Date is linked to date in the other table.

When I select a month e.g. jan 2009, everything becomes empty. When I change it back to a date 01.01.2009, than I see the costs for that day. I want to see al cost for januari when I select januari. Whats wrong?

Change date field

Thu, 24 Sep 2009 18:35:27 GMT

I fixed it. It was a problem with the standard date settings.