Using Max(Date) in Interval

Thu, 15 Aug 2013 07:35:25 GMT

Hey,

So I'm looking to find out how many people have lived in a place for a certain amount of time. However they can have multiple tenancies, so I want to only use the most recent one.

This is the sort of formula I'm looking at

=sum(if(PBI='Yes' AND Interval(Date([Tenancy End Date])-Date([Tenancy Start Date]), 'DD/MM/YYYY') >= 180, 1, 0))

But as mentioned I want to use the latest tenancy, so I'm trying

=sum(if(PBI='Yes' AND Interval(Date([Tenancy End Date])-Date(Max([Tenancy Start Date]), 'DD/MM/YYYY') >= 180, 1, 0))

However, that's throwing an error. Some quick googling has suggested it's because I'm using an aggregate function within another one, but I'm not sure how to get around that.

If it possible to do anything with directly editing the script? I wasn't directly involved with the development, so I'm just working on a sheet on a document using QVD (And I have no idea where the files that created the QVD files are located)

Many thanks,
Joseph Garner.

P.S If the general =sum(if(etc)) code is poorly optimised, please also mention that.

topic Using Max(Date) in Interval in QlikView

Using Max(Date) in Interval