Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
This looks to be along the right lines but you're missing a )
=sum({<Referral={'Yes'}, Date = {"<=$(addyears(max(Date),-1))"}>}Name)
Adam is right, but you are calculation is name. Does name field has values or characters
You might also need an '=' like:
=sum({<Referral={'Yes'}, Date = {"<=$(=addyears(max(Date),-1))"}>}Name)
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)
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!!