Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Substraction of Date is not working properly

Hi All,

 

I have two data fields BusBreachDate_New and Stop time.

I want to subtract  BusBreachDate_New  -  Stop time , and check whether the result is +ve or -ve.

For one Incident, INC1010490, it coming wrong. Others are fine.

Could you please help me to understand. Not able to rectify.

 

Thanks,

Sarif

1 Solution

Accepted Solutions
Highlighted

Re: Substraction of Date is not working properly

You are manipulating the time format of the date field in the Final tab of the script to add 10:00:00 to the date format but are not amending the numeric value of the date. Date calculations are done on the numeric value of the date field not the formatted text.

IF(BusBreachDate_On_Holiday = 'YES' or CreateDate_On_Holiday = 'YES',
TIMESTAMP(BusBreachDateFormat + 1,'MM/DD/YYYY 10:00:00'),BreachTime_New1) AS BusBreachDate_New,

The num value of BusBreachDate_New  (43286) is for 07/05/2018 00:00:00  not 07/05/2018 10:00:00  hence the discrepancy in your calculation.  You need to add 10:00:00 to the field value not the format.

interval.JPG

View solution in original post

6 Replies
Highlighted
Specialist
Specialist

Re: Substraction of Date is not working properly

Have you tried with interval

interval(Stop time, BusBreachDate_New)

Highlighted
Creator II
Creator II

Re: Substraction of Date is not working properly

Yes I used interval only.

 

= interval(Timestamp(BusBreachDate_New,'MM/DD/YYYY hh:mm:ss') - Timestamp([Stop time],'MM/DD/YYYY hh:mm:ss'),'ss')

Highlighted

Re: Substraction of Date is not working properly

Your data is already correctly formatted as dates so this works.

interval( [Stop time]- BusBreachDate_New , 'D hh:mm')

 

 

Highlighted
Creator II
Creator II

Re: Substraction of Date is not working properly

Sorry I have mistakenly make opposite of expression :

it should be :

BusBreachDate_New - StopTime.

I have rectified in my post.

The scenario I have posted, BusBreachDate_New is 07/05/2018  10:00:00 and StopTime is 07/05/2018 04:20:27.

Though BusBreachDate_New  is greater than StopTime, it is coming as negative.

This is my actual question.

Please check for Number = INC1010490.

 

Thanks in advance for your understanding.

 

Thanks,

Sarif

Highlighted

Re: Substraction of Date is not working properly

You are manipulating the time format of the date field in the Final tab of the script to add 10:00:00 to the date format but are not amending the numeric value of the date. Date calculations are done on the numeric value of the date field not the formatted text.

IF(BusBreachDate_On_Holiday = 'YES' or CreateDate_On_Holiday = 'YES',
TIMESTAMP(BusBreachDateFormat + 1,'MM/DD/YYYY 10:00:00'),BreachTime_New1) AS BusBreachDate_New,

The num value of BusBreachDate_New  (43286) is for 07/05/2018 00:00:00  not 07/05/2018 10:00:00  hence the discrepancy in your calculation.  You need to add 10:00:00 to the field value not the format.

interval.JPG

View solution in original post

Highlighted
Creator II
Creator II

Re: Substraction of Date is not working properly

Thanks a lot Colin. Now I understand the issue.

So I need to add this in script level, I am right?

 

I am using below script. Hope this will work. I will test and let you know, if any further help required.

 IF(BusBreachDate_On_Holiday = 'YES' or CreateDate_On_Holiday = 'YES',
TIMESTAMP(BusBreachDateFormat + 1,'MM/DD/YYYY')+10/24,BreachTime_New1) AS BusBreachDate_New