topic Re: How to do a count on a max(date) - one year expression in QlikView

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

samantha92 — Wed, 21 Sep 2016 08:43:36 GMT

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.

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

adamdavi3s — Thu, 22 Sep 2016 08:15:46 GMT

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

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

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

Anil_Babu_Samineni — Thu, 22 Sep 2016 08:45:44 GMT

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

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

tresB — Thu, 22 Sep 2016 08:49:52 GMT

You might also need an '=' like:

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

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

sunny_talwar — Thu, 22 Sep 2016 09:20:48 GMT

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)

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

samantha92 — Thu, 22 Sep 2016 12:52:07 GMT

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!!