I am working in Qlik Sense building a Straight Table, attempting to show a flag if another Count() expression = 0 (or <1, either way) but I can't seem to make it work.
Client ID Active Agency Active Program If Active Agency If Active Program If No Active Program
001 1 2 Y Y -
002 1 0 Y - -
003 0 0 - - -
For Client ID 002 & 003 above, I should be getting a Y in If No Active Program, but I can't seem to get it to work.
Here are my expressions:
Active Agency = Count({$<[Agency Enrollments.Status] = {'ACTIVE'}>}[Agency Enrollments.Agency Enrollment ID])
Active Program = Count({$<[Program Enrollments.Status] = {'ACTIVE'}>}[Program Enrollment ID])
If Active Agency = if(Aggr(Count({$<[Agency Enrollments.Status] = {'ACTIVE'}>}[Agency Enrollments.Agency Enrollment ID]), [Client ID]) > 0, 'Y')
If Active Program = if((Aggr(Count({$<[Program Enrollments.Status] = {'ACTIVE'}>}[Program Enrollment ID]), [Client ID]) > 0), 'Y')
If No Active Program = if((Aggr(Count({$<[Program Enrollments.Status] = {'ACTIVE'}>}[Program Enrollment ID]), [Client ID]) = 0), 'Y')
I would just do a Column() function, but the ultimate goal is to put this in a KPI where I can't make that Column reference. When I remove the Aggr() part from the Is No Active Program expression, it works, but like I said, my goal is to have this be in a KPI, not a Straight Table, so I need to keep that in there, unless I'm missing something and there's another way to do it.
I appreciate any help!
Thank you!
