Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
New Contributor III

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

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.

1 Solution

Accepted Solutions
Highlighted

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

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)

View solution in original post

5 Replies
Highlighted
Honored Contributor

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

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

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

Highlighted

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP
MVP

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

You might also need an '=' like:

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

Highlighted

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

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)

View solution in original post

Highlighted
New Contributor III

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

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