two times - subtraction and negatives?

[josephinetedesc]

Hi All

in Qlikview I use the following expression:

time(interval(.timeAdmin.timeArrive),'mm')

but when the arrival time is BEFORE the Admin time I get an odd answer:

AppointmentDate     timeAdmin     ArrivedTime     admit - arrival

4/08/2017               14:30:00          14:30:00       47                       #######     -13

  47 IS INCORRECT I can see that  (47 - 60) would give -13

but how do I do it in Qlikview?

I am getting the dates from 2 different systems - I think the format is the same (originally it was a problem because one showed AM and the other did not):  Then normally it would be a full time stamp but not in one of the systems!

time(AdmissionDateTime,'hh:mm:00') as timeAdmin,

time(ArrivedTime,'hh:mm:00') as timeArrive,

This was painful to do even in Excel!

Thank you!

Jo

[Anil_Babu_Samineni]

Please share the image where you getting wrong values while subtract?

[josephinetedesc]

should show in the excel worksheet?

In Qlikview the answer shows as column 4 - i have highlighted the problem rows.

[Or]

You're generating a value of -0.009 or so (AKA -13 minutes). Formatting negative values as time is a little quirky, but the result is probably 23:47:00 in this case.

It seems like what you actually need is Interval(timeAdmin-timeArrive).

[Anil_Babu_Samineni]

Then, Use this?

=Time(Interval(FieldName, FieldName2), 'MM')

Update

[josephinetedesc]

I actually get the answer of 47 in Qlikview - I get the answer of -13 only in Excel.

I used the formula: time(interval(.timeAdmin.timeArrive),'mm')

but the expression you suggested gives me the same thing ... no negatives ...

[Or]

Why are you formatting the interval as a time part?

If you're trying to convert the value into minutes, that's probably not the right way to go about it. Try:

(TimeAdmin - TimeArrive)*(24*60)

Obviously you can just multiply by 1440, I left it verbose to make it clear I was multiplying by hours and minutes. This will convert the value (which is stored as days) into minutes.

[avinashelite]

In your case I don't think you need the interval try directly

(TimeAdmin - TimeArrive)*(24*60)

[Anonymous]

Hi,

try this formula :

if( CDU.CDU_TTT.PatientArrivedTime>IMP.timeAdmin,-1*minute(Timestamp( CDU.CDU_TTT.PatientArrivedTime-IMP.timeAdmin,'mm')), minute(Timestamp(IMP.timeAdmin- CDU.CDU_TTT.PatientArrivedTime,'mm'))) as INTERVAL

I'm not sur it's the easiest way to do it but it should work !

[josephinetedesc]

Hi Anne

no the same thing ... 47 should be -13 ... when timeAdmin is > ArrivedTime it needs to be taken away from 60.  I suppoise i should do an if statement?

Jo