topic Re: Dates in QlikView

Dates

Wed, 29 Jun 2011 00:03:45 GMT

Another novice question...

I have a spreadsheet with lots of data against DateRaised, so for example

01/01/11 RTA

02/01/11 Injury

03/01/11 Injury

28/05/11 RTA

29/05/11 RTA

30/05/11 RTA

31/05/11 Injury

How do I create a list box with Jan, Feb.......Dec, so users can select a month and see the totals

Do I need to do something in the load script? I've been searching other discussions and can't quite work out what I need to do.

Thanks

Dates

Wed, 29 Jun 2011 00:08:45 GMT

Is the data above all in a single field? or multiple?

Either way you just need to add a couple lines to your load script.

Load DateRaised,

month(DateRaised) as month,

year(DateRaised) as year,

If the data is in a single field then do

Load DateRaised,

month(left(DateRaised,8) as month,

Dates

Anonymous — Wed, 29 Jun 2011 00:11:12 GMT

Hi Melanie,

I assume you have a load statement in the script like:

Load

DateRaised,

Type

From xxxx;

You can create a new Month field there by doing something like this:

Month(Date#(DateRaised, 'DD/MM/YY')) as Month

The Date#() function is to properly identify the DateRaised as a date value so that you can use Month() to extract the month.

Re: Dates

Wed, 29 Jun 2011 00:20:32 GMT

Ok, but I'm just getting a whole list of month names returned, so

Jan

How do make it so it selects all the Januarys?

So I need a list box with

Jan

Feb

Mar

etc

and when a user selects a month it displays all the values for that month

Re: Dates

Wed, 29 Jun 2011 00:29:05 GMT

Just add the newly created month field to a list box on the page and it will get you where you want to go!

Re: Dates

Anonymous — Wed, 29 Jun 2011 00:37:30 GMT

Ah, sorry about that, try this instead:

Month(Monthstart(Date#(DateRaised, 'DD/MM/YY'))) as Month

This should do the trick hopefully.