topic Using a variable inside set analysis in QlikView

Using a variable inside set analysis

trmcbrad — Mon, 08 Aug 2016 17:36:14 GMT

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!

Re: Using a variable inside set analysis

swuehl — Mon, 08 Aug 2016 18:15:25 GMT

Try

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

with a definition like

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

Re: Using a variable inside set analysis

trmcbrad — Mon, 08 Aug 2016 21:05:16 GMT

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!

Re: Using a variable inside set analysis

swuehl — Mon, 08 Aug 2016 21:42:34 GMT

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;

Re: Using a variable inside set analysis

trmcbrad — Tue, 09 Aug 2016 13:29:10 GMT

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!

Re: Using a variable inside set analysis

swuehl — Tue, 09 Aug 2016 18:12:55 GMT

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.