Substract Dates

[#1]

Hi Everybody.

I´m new in Qlikview.

I need help in order subtract two dates, i got this dates in two table fields call "Fecha de Apertura" and "Inicio de Afectación" the idea is to obtain something like "DD, hh:mm:ss" after the subtract, i use only  [Fecha Apertura]-[Inicio de Afectacion] but it doesn´t work, how can i do??

Best Regards

[#2]

What are you getting when you subtract the dates? A decimal number?

The result should be the number of days. To get it in days, minutes, etc... you will need to convert that yourself.

Here is an example for days/hours/minutes: Floor(([Fecha Apertura]-[Inicio de Afectacion])) & 'd  ' & Floor(Fmod(([Fecha Apertura]-[Inicio de Afectacion]), 1) * 24) & 'h  ' & Round(Fmod(Fmod(([Fecha Apertura]-[Inicio de Afectacion]), 1) * 24, 1) * 60) & 'm'

It should return in the format: 4d 3h 14m

[#3]

Hi Kramelot, use something like this:

tmp:
LOAD * INLINE [
    FechaApertura, InicioAfectación
    05/01/2011 12:25:00, 01/01/2011 10:15:50
];
data:
load 
interval(FechaApertura-InicioAfectación,'DD, hh:mm:ss') as SubstractDate
resident tmp;

Hope this help

[#4]

Thanks for your answer...

i used your suggestion but only appears d h m, without any value, and the date format is equal than the system.

I attached the QV document.

B. R

[#5]

Hi.

I tried your suggestion but it doesn´t work.

Thanks

[johnw · Accepted solution]

Your dates are not dates or timestamps.  They are text fields.  You can't subtract text fields.

You can probably read them in as timestamps by changing your TimestampFormat to match:

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

But I can't test because you're loading from a file you didn't attach. 

You can't put a load with no source after the main load and expect anything to happen.  That sort of expression needs to be in the main load.

interval([Fecha Apertura]-[Inicio de Afectacion|Inicio de la interrupcion de servicio],'DD, hh:mm:ss') as [Tiempo de Apertura],

And that's assuming it interprets the timestamps correctly with the fixed format.  Otherwise it gets more complicated.

[#7]

Thanks a lot Mr John.

The problem was TimestampFormat, i make your suggestion and now it works.

Best Regards.