Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gauravgg
New Contributor III

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
Honored Contributor III

Re: how to subtract date and get number of days?

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
Contributor

Re: how to subtract date and get number of days?

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
Contributor

Re: how to subtract date and get number of days?

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 

Re: how to subtract date and get number of days?

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

Re: how to subtract date and get number of days?

In script simply try

floor(verified_date) - floor(registered_date) as No_of_days

rgvavihs
Valued Contributor

Re: how to subtract date and get number of days?

Verified_date_Registere_Date

Community Browser