Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
valerio347
Contributor
Contributor

Calculation of Days Between two Dates without counting the hours

Hi I have a question,

 

I ve been trying these for weeks but without help.

 

I have a table with two fields: DATE_A , DATE_B

The measure for DATE_A is: DD/MM/YYYY and Hours  (e.g. 12/06/2019 14:00:00)

The measure for DATE_B is: DD/MM/YYYY (e.g 14/06/2019)

 

My need is to calculate the days between these field without considering the hours inside the calculation.

 

I want the system to don't consider the hours inside the calculation. 

Example:

31/05/2019 15:01:19     -    01/06/2019 =  0,37  days    [ACTUAL]

31/05/2019 -  01/06/2019 = 1,00 days  [EXPECTED NEED]

 

I want to do this because

 

 

Could you please give me the right formula for this calculation?

 

 

Thank you in advance.

2 Replies
HirisH_V7
Master
Master

use this in Script:

 

LOAD *,
     Interval(Num(DateB)-Floor(Timestamp#(DateA_Mod,'DD/MM/YYYY h:mm:ss')),'DD')  as Diff,
     Date(Floor(Timestamp#(DateA_Mod,'DD/MM/YYYY h:mm:ss'))) as DateA
 INLINE [
    DateA_Mod,DateB
    31/05/2019 15:01:19,01/06/2019 
    

];

 

In UI:

 

=Interval(DateB-Floor(Timestamp#(DateA,'DD/MM/YYYY h:mm:ss')),'DD')

 

HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
valerio347
Contributor
Contributor

Thanks a lot I will try it this monday at work.

What if i have DATE_A and DATE_B both in Days and Hours, but i want to make the calculation of days only in DATE( and not hours)?


Example:
DATE_A is: DD/MM/YYYY and Hours (e.g. 12/06/2019 14:00:00)

DATE_B is: DD/MM/YYYY and Hours (e.g. 13/06/2019 18:00:00)