Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gauravgg
Partner - Creator
Partner - Creator

how to subtract date and get number of days?

hi

i have a table in which i have register date and verified date

customer_id register_dateverified_date
101/01/201610/01/2016
220/01/201625/01/2106

in want to subtract verified_date - register_date and add a column no_of_days

thanks in advance

6 Replies
oknotsen
Master III
Master III

Assuming they are actual dates, you can just do that.

In the front-end you will probably have to add sum to it:

sum(verified_date) - sum(register_date)

... but only do so if you are 100% sure they are a single value per customer_id

I would suggest you do this in the script by adding a field to that table like this:

verified_date - register_date as no_of_days,

May you live in interesting times!
mauricewehbe
Creator
Creator

Hi Gaurav,

By just subtracting 2 dates you get the number of days:

=Date1-Date2

But of course if your have lot of rows i will recommend as Onno to create a calculated field in your loading script.

Hope this help

Maurice

mauricewehbe
Creator
Creator

For the script here is an example using interval also:

MyData: 

LOAD

verified_date,

register_date, 

Interval(verified_date-register_date,'d') as Nbdays

...


If you want to force diff date even when first date less than max date use:

Fabs(Interval(verified_date-register_date,'d')) as Nbdays


Best,

Maurice 

sunny_talwar

Like everyone has mentioned above me that if you need the difference between the two dates, you can  just subtract the smaller date from the larger date. If you just want to know the working days between the two dates, you can use NetWorkDays() function.

Find Net Working Days

Kushal_Chawda

In script simply try

floor(verified_date) - floor(registered_date) as No_of_days

rgvavihs
Specialist
Specialist

Verified_date_Registere_Date