topic Date to integer in QlikView

Date to integer

hopkinsc — Tue, 13 Aug 2013 09:54:31 GMT

hi all, can anyone help me on how to convert a date field (MMM-YYYY) to an interger?

Re: Date to integer

Tue, 13 Aug 2013 09:55:13 GMT

num(datefield)

Re: Date to integer

MayilVahanan — Tue, 13 Aug 2013 09:56:02 GMT

Try like this

= Floor(dateField)

Hope it helps

Re: Date to integer

hopkinsc — Tue, 13 Aug 2013 09:57:39 GMT

Hi, i am not sure what i am doing wrong but if i create a text box and use =num('JUL-2013') i get null.

Re: Date to integer

rustyfishbones — Tue, 13 Aug 2013 09:58:01 GMT

Try this

Re: Date to integer

Tue, 13 Aug 2013 09:59:47 GMT

TRY ANY ONE OF THEM

ROUND(dateField)

FLOOR(dateField)

NUM(dateField)

=ROUND(date(today(),'MMM-YYYY' ))

=NUM(date(today(),'MMM-YYYY' ))

OR THIS ONE

=NUM(date(DATEFIELD,'MMM-YYYY' ))

Re: Date to integer

MayilVahanan — Tue, 13 Aug 2013 10:00:09 GMT

=num(Date#('JUL-2013' , 'MMM-YYYY'))

Hope it helps

Re: Date to integer

Tue, 13 Aug 2013 10:00:58 GMT

num('JUL-2013')

Here, you are trying to convert a string to number. Qlikview doesn't understand it unless you tell him that it's a date:

num(Date#('11-Jul-2013', 'DD-MMM-YYYY'))

Re: Date to integer

Tue, 13 Aug 2013 10:01:25 GMT

TRY THIS

NUM(DATE#('JUL-2013','MMM-YYYY'))

Re: Date to integer

hopkinsc — Tue, 13 Aug 2013 10:06:50 GMT

Hi Everyone,

Thanks for your replies although i cannot seem to get any of them to work correctly.

I have attached a sample, could someone show me the correct way on the sample please?

So i want the current date format 'JUL-2013' to show the number format (month start)

Thanks

Re: Date to integer

Tue, 13 Aug 2013 10:10:01 GMT

You are writting this:

=num(Date#('Jul-2013', 'DD-MMM-YYYY'))

But it won't work because the formula its expecting the day and you only write jul-2013.

Try writting 01-jul-2013... or =num(Date#('Jul-2013', 'MMM-YYYY'))

Re: Date to integer

MayilVahanan — Tue, 13 Aug 2013 10:12:22 GMT

Temp:

LOAD * INLINE [

Date, Sales

May-2013, 1000

Jun-2013, 2000

];

Date:

Load *,

Num(Date#(Date,'MMM-YYYY')) as DateField

Resident Temp;

Drop table Temp;