topic Re: How num() works for any date value as an argument? in QlikView

How num() works for any date value as an argument?

Fri, 18 Oct 2013 06:51:57 GMT

Hello,
I am new to Qlikview. I was looking at the piece of code:

let v_counter=Num(addmonths(MonthStart(Today()),-3));

I ran each of the subfunctions seperately in order to learn what was being returned. Now, addmonths of the above example returns 7/1/2013 ///Please note: this format is MM/DD/YYYY

Num() function must be working on this date and it returned 41456. So, value of v_counter is 41456.
My question is how Num works on this date value as an argument and how it converted 7/1/2013 to 41456? I serched in Help but could not extract much.

Thank you.

Re: How num() works for any date value as an argument?

struniger — Fri, 18 Oct 2013 06:57:42 GMT

Have a look at this excellent post:

QlikView Addict: Dates in QlikView - Part 1

Re: How num() works for any date value as an argument?

rajni_batra — Fri, 18 Oct 2013 07:10:35 GMT

In every system like excel or qlikview or anyother software Date is stored in num format like if u write

date(1) it will give u '31-12-1899' which is minimum date stored in qlikview.

Like this it every date is stored as num so when u use num() function is returns u the number of that date.

Hope this clears ur doubt !!!

Re: How num() works for any date value as an argument?

Fri, 18 Oct 2013 07:12:21 GMT

Its the function of qlikview, just like excel

Read this:

http://community.qlik.com/docs/DOC-3102

and this:http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

Re: How num() works for any date value as an argument?

Fri, 18 Oct 2013 07:20:14 GMT

Thanks Rajni !!!

I understood the concept and I checked it like date(41456), which returned 7/1/2013

Re: How num() works for any date value as an argument?

AbhijitBansode — Fri, 18 Oct 2013 07:42:59 GMT

The first thing you need to know about dates in QlikView is that they are really stored as a dual value.
This value contains both a string (such as "01/01/2012") representing the date and number (such as "40909") defining the number of days since the epoch date.

Epoch date is a date chosen as the origin of a particular era.

QlikView epoch date is the same as the Oracle epoch date, 30th December 1899.

Try creating a text object in QlikView and add the following expression:

=num(makedate(1899,12,30))

you will get result 1.

and now change the expression to

=num(makedate(1900,1,1))

you will get result 2

This means 30th December 1899 numeric value is 1, 1st January 1900 numeric value is 2 and so on.

Dates prior to this base date are simply stored as a negative number of days.

Qlikgards,

Abhijit