topic Re: difference between 2 dates with aggr function in QlikView

difference between 2 dates with aggr function

Fri, 09 May 2014 10:02:21 GMT

HI All,

I want difference between max(notification date) and min(notification date) for that particular functional location. but i also want to display notification dates also if i am using date(Min(( aggr ( MIN ( [Notification Date]),[Functional Location Code] ) )))

this expression s working but if 2 dates are there then for 2 nd row it will return blank

HOw to take difference between this 2 dates for 2 notification nos.

Re: difference between 2 dates with aggr function

Gysbert_Wassenaar — Fri, 09 May 2014 10:06:51 GMT

Please post some sample data and the result you expect to see based on that data.

Re: difference between 2 dates with aggr function

Fri, 09 May 2014 10:13:27 GMT

Hi Gysbert Wassenaar,

Suppose A is Functional location and against that 101is notification no for that notification no I have 2 rows i.e,

20 APR 2014 and 29 Apr 2014 so in want diff. 9 in 2 rows as follows :

Fuctional Location Notification No Date Output

A 101 20/04/2014 9

101

29/04/2014

Re: Re: difference between 2 dates with aggr function

Gysbert_Wassenaar — Fri, 09 May 2014 10:19:47 GMT

See attached qvw.

Re: difference between 2 dates with aggr function

anbu1984 — Fri, 09 May 2014 10:40:38 GMT

In Script:

Table:

LOAD * INLINE [

Fuctional Location, Notification No, Date

A, 101, 20/04/2014

A, 101, 29/04/2014

];

Join

Temp:

Load [Fuctional Location],[Notification No],Max(Date)-Min(Date) As Diff Resident Table

Group by [Fuctional Location],[Notification No];

Re: difference between 2 dates with aggr function

Fri, 09 May 2014 10:54:46 GMT

Hi Gysbert Wassenaar,

Thankx for reply it is working properly. But now i want diff out put as follows :

Function Location Notification Date Output

A 10/04/2014 2

A 12/04/2014 4

A 16/04/2014 0

Like that , It means difference between first notification date and next notification date and if there is no next notification date then 0 like this I want so how to write expressions for that.

Re: difference between 2 dates with aggr function

Gysbert_Wassenaar — Fri, 09 May 2014 10:58:12 GMT

Use Function Location and Notification Date as dimensions and as expression alt(below([Notification Date])-[Notification Date] ,0)