Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hello Jon!
I use this solution because is intuitive and very usefull to KPI, you will verify any field and your condition, if it's true, take one count...can be used "else" too.
SUM( IF(Text([Agency Enrollments.Status])='ACTIVE',
//if it's ACTIVE take count
1,
//else
IF(Text([Program Enrollments.Status] )='ACTIVE',
1,
//else
0)))
Good luck have fun!