Calculate interval between dates with condition

[#1]

Hi,

I am new with Qlik Sense and I am trying to calculate interval between dates with condition.

I have two tables with a same ID (IDEmb):

IDEmb	Date	PT
CPX01	17/01/2012	CPX
CPX02	21/08/2012	CPX
CPX03	15/01/2013	CPX
CPX04	06/11/2013	CPX
CPX05	01/04/2014	CPX
CPX06	12/06/2014	CPX
PPK01	06/11/2013	PPK
PPK02	01/04/2014	PPK
PPK03	12/06/2014	PPK

IDEmb	Manu	Type
CPX01	AA	Meteo
CPX01	BB	Meteo
CPX01	DD	N/A
CPX02	BB	Meteo
CPX03	CC	Ocean
CPX04	BB	Meteo
CPX05	BB	Meteo
CPX06	AA	Meteo
CPX06	CC	Ocean
PPK01	AA	Ocean
PPK02	BB	Ocean
PPK02	CC	Meteo
PPK02	AA	Ocean
PPK02	TT	Meteo
PPK03	AA	Ocean
PPK03	BB	Meteo
PPK03	EE	Ocean

I would like to have the interval between the dates base on the Type and PT like the following tables:

IDEmb	Date	PT	Type	Interval
CPX01	17/01/2012	CPX	Meteo	-
CPX02	21/08/2012	CPX	Meteo	217
CPX04	06/11/2013	CPX	Meteo	442
CPX05	01/04/2014	CPX	Meteo	146
CPX06	12/06/2014	CPX	Meteo	72

IDEmb	Date	PT	Type	Interval
CPX03	15/01/2013	CPX	Ocean	-
CPX06	12/06/2014	CPX	Ocean	513

IDEmb	Date	PT	Type	Interval
PPK02	01/04/2014	PPK	Meteo	-
PPK03	12/06/2014	PPK	Meteo	72

IDEmb	Date	PT	Type	Interval
PPK01	06/11/2013	PPK	Ocean	-
PPK02	01/04/2014	PPK	Meteo	146
PPK03	12/06/2014	PPK	Meteo	72

I have managed to do a graph but I can’t calculate the avg, min, max.

I used the expression:

if(only({<Type = {'Meteo'}>}Emb),Emb-above(total Emb),0) but is using the date above and not the date with Type = {'Meteo'} and the avg, min, max don’t work

I have tried to create new table when II import the data but is not working.

Thanks,

Patrice

[Gysbert_Wassenaar]

See attached qvw

[#3]

Hi Gysbert,

if(above(total PT)=PT and above(total Type) = Type, Date-above(total Date),0)

The expression doesn't work properly. The PT condition is perfect but the Type condition don't work. So every value is 0. I try to change a bit the expression but it was not successful.

I think that the problem is because you can have several type of "Type" in the same "IDEmb".

Any idee to resolve this?

I will try (monday) to create a new colunm when I import the data with this expression [if(Previous(total PT)=PT , Date-Previous(total Date),0) and use a sum() in mesure.

Thanks

Patrice

[#4 · Accepted solution]

Hi,

I resolve the problem with a JOIN during "load data":

MOcea:

LOAD DISTINCT

    IDEmb,

    Type as TypeOcea  

FROM [lib://Downloads/RDE_Database_Clean.xlsx]

(ooxml, embedded labels, table is M)

WHERE Type = 'Ocean';

LEFT JOIN (MOcean)

LOAD

  IDEmb,

    PT as PTOcean,

    Date as DateOcean

FROM [lib://Downloads/RDE_Database_Clean.xlsx]

(ooxml, embedded labels, table is Emb);

LEFT JOIN (MOcea)

LOAD

   IDEmb,

    If(Previous(PTOcean)=PTOcean, DateOcean-Previous(DateOcean)) as IOcea 

RESIDENT MOcea;

DROP FIELD DateOcean, PTOcea;

MMeteo:

LOAD DISTINCT

    IDEmb,

    Type as TypeMeteo 

FROM [lib://Downloads/RDE_Database_Clean.xlsx]

(ooxml, embedded labels, table is M)

WHERE Type = 'Meteo';

LEFT JOIN (MMeteo)

LOAD

  IDEmb,

    PT as PTMeteo,

    Date as DateMeteo

FROM [lib://Downloads/RDE_Database_Clean.xlsx]

(ooxml, embedded labels, table is Emb);

LEFT JOIN (MOcea)

LOAD

   IDEmb,

    If(Previous(PTMeteo)=PTMeteo, DateMeteo-Previous(DateMeteo)) as IOcea

RESIDENT MOcea;

DROP FIELD DateMeteo, PTMeteo;

Patrice