Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

trmcbrad
New 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
MVP
MVP

Re: Using a variable inside set analysis

Try

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


with a definition like

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

5 Replies
MVP
MVP

Re: Using a variable inside set analysis

Try

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


with a definition like

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

trmcbrad
New Contributor

Re: Using a variable inside set analysis

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!

MVP
MVP

Re: Using a variable inside set analysis

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
New Contributor

Re: Using a variable inside set analysis

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

MVP
MVP

Re: Using a variable inside set analysis

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.

Community Browser