Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
trmcbrad
Contributor
Contributor

Using a variable inside set analysis

I'm having an issue using a variable I created inside set analysis.

I'm using this formula:

=count({<ProviderFullNM=, CareProcessNM ={$(vCPRK1)},ThreeCaseMinFLG = {'Y'}>}FacilityAccountID)

I'm trying to display the value in a text box but I'm getting the "Error in set modifier ad hoc element list" error.

The value of the variable does however display in a list box object.

I set the variable in the script as below:

SET vCPRK1 =if(aggr(rank(count(DISTINCT FacilityAccountID)),CareProcessNM)='1',CareProcessNM);

I've tried every variation I can find to get this to work and I can't seem to get it working. Can someone please help?

Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

=count({<ProviderFullNM=, CareProcessNM ={"=$(vCPRK1)"},ThreeCaseMinFLG = {'Y'}>}FacilityAccountID)


with a definition like

SET vCPRK1 = rank(count(DISTINCT FacilityAccountID))=1;

View solution in original post

5 Replies
swuehl
MVP
MVP

Try

=count({<ProviderFullNM=, CareProcessNM ={"=$(vCPRK1)"},ThreeCaseMinFLG = {'Y'}>}FacilityAccountID)


with a definition like

SET vCPRK1 = rank(count(DISTINCT FacilityAccountID))=1;

trmcbrad
Contributor
Contributor
Author

How can I account for a tie in this ranking? Currently when there are two Care Processes with the same case counts the variable does not work when being used in the set modifier.

Thanks for your help!

swuehl
MVP
MVP

You should be able to achieve this using the optional arguments to rank, these control the function behaviour in case of ties.

Try

SET vCPRK1 = rank(count(DISTINCT FacilityAccountID),1)=1;

trmcbrad
Contributor
Contributor
Author

I'm still getting some ties using this formula. Is there another field i can use to break this tie? Could it be something like the avg cost of the CareProcess that would break the tie?

Thanks again for your help!

Capture.JPG

swuehl
MVP
MVP

If that's what you want to use to create your rank and use it as tie breaker, you can maybe use it like

SET vCPRK1 = Rank( Count(DISTINCT FacilityAccount) +1/Sum(Cost) ,1) =1

The basic idea is, that the count will return an integer, to break the ties for the same count, add a small number < 1.

1/ Sum(Cost) will return (assuming Sum(Cost) is larger than 1) a small number <1, the less cost the higher the value.

Hence Care processes with least count will be ranked top on processes with same count.