How to do a count on a max(date) - one year expression

[samantha92]

Hi,

I have an issue where I am trying to do a count on the following expression:

if(max(Date<=Today()-365), Max(Date))

As soon as I add a count I get a nested aggr not allowed error.

I tried using set analysis like so:

=sum({<Referral={'Yes'}, Date = {"<=$(addyears(max(Date),-1)"}>}Name)

But get a error in set modifer ad hoc element list: ',' or ')' expected.

I have also tried something like this:

Count(If(Aggr(NODISTINCT Max(date), id) = date, id))

But this just gives me errors also.

How do I do a simple count on a max(date) minus one year?

Thanks in advance.

[adamdavi3s]

This looks to be along the right lines but you're missing a )

=sum({<Referral={'Yes'}, Date = {"<=$(addyears(max(Date),-1))"}>}Name)

[Anil_Babu_Samineni]

Adam is right, but you are calculation is name. Does name field has values or characters

[tresB]

You might also need an '=' like:

=sum({<Referral={'Yes'}, Date = {"<=$(=addyears(max(Date),-1))"}>}Name)

[sunny_talwar · Accepted solution]

I think there is no way you can Sum(Name) assuming Name is text... try this:

=Count({<Referral={'Yes'}, Date = {"<=$(=AddYears(Max(Date), -1))"}>}Name)

You further might need to add Date() function if you date format is different then the QlikView's default's dateformat set during the reload in the environmental variable

=Count({<Referral={'Yes'}, Date = {"<=$(=Date(AddYears(Max(Date), -1), 'DateFieldFormatHere'))"}>}Name)

Finally Do you need a distinct count?

=Count(DISTINCT {<Referral={'Yes'}, Date = {"<=$(=Date(AddYears(Max(Date), -1), 'DateFieldFormatHere'))"}>}Name)

[samantha92]

Ah of course, always helps when you have another set of eyes looking at something to spot your mistakes

I have changed name to ID and added the date

Thank you everyone for your help!!