topic Re: Multiple values in set expression in QlikView

Multiple values in set expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a variable, vYear=GetFieldSelections(Year) and a set expression using this variable -

Count({<Year={"$(=$(vYear))"}>}Distinct [Groups Key]).

The above set expression works when a single value is selected in the Year filter, but when I select multiple values in the filter the set expression returns null. Can anyone please guide me how to fix this expression ?

Appreciate your help !

Thanks,

Deepika

Re: Multiple values in set expression

Gysbert_Wassenaar — Mon, 02 Jan 2017 06:59:33 GMT

If you want the selections in the field Year to be applied to the expression then you can use this expression:

Count(Distinct [Groups Key])

There's no need for set analysis because the selections you make are the same that you want to use for calculating the expression.

Re: Multiple values in set expression

Mon, 02 Jan 2017 07:11:46 GMT

Hi Gysbert,

Thanks for the response.

The chart where I am using the set expression uses a fact table which is linked to master calendar, but the Year filter is not referencing the Master calendar, but a different table not linked to the master calendar. This is the reason for the set expression.

Deepika

Re: Multiple values in set expression

Gysbert_Wassenaar — Mon, 02 Jan 2017 07:18:41 GMT

That's not possible. You're using the field 'Year' in both the getfieldselections function and the count expression. You are using the same field 'Year' in both cases. So you get exactly the same selections.

There can only be one field 'Year' in the data model. If two or more tables have that same field 'Year' then those tables are linked. That's the way Qlikview works.

Re: Multiple values in set expression

Mon, 02 Jan 2017 07:53:50 GMT

Sorry, the set expression should have been like this - Count({<MC_Year={"$(=$(vYear))"}>}Distinct [Groups Key]).

Re: Multiple values in set expression

saimahasan — Mon, 02 Jan 2017 08:08:27 GMT

Can you please post a sample application.

Re: Multiple values in set expression

Anonymous — Mon, 02 Jan 2017 08:35:31 GMT

Hi, try this:

vYear=CONCAT(DISTINCT Year,',')

Count({<Year={$(vYear)}>}Distinct [Groups Key])

Regards!!

Re: Multiple values in set expression

Gysbert_Wassenaar — Mon, 02 Jan 2017 16:42:35 GMT

Ok, then try this one: Count({<MC_Year=$::Year>}Distinct [Groups Key])

Re: Multiple values in set expression

lorenzoconforti — Mon, 02 Jan 2017 18:34:31 GMT

This drove me nuts too; I have implemented something similar in a dashboard I worked on recently

It's a two step approach as Manuel has indicated.

First, populate the variable and format it as per below (take note of ' and "); please note I had to assign * value for my expression to work when no Year was selected:

vYear=if(isnull(GetFieldSelections(Year)), '*', Replace(Replace(GetFieldSelections(Year),' ',''),',','","'))

Try use the following in your set expression:

=count({<Year = {"$(vYear)"}>} Distinct [Groups Key])

Hope it helps

Lorenzo

Re: Multiple values in set expression

Anonymous — Tue, 03 Jan 2017 06:56:19 GMT

Hi Lorenzo, with concat function you don't need to use *.

Regards!

Re: Multiple values in set expression

pathiqvd — Tue, 03 Jan 2017 07:16:02 GMT

Hi,

Try This,

=count({<Year1=P(Year2)>} Distinct [Groups Key])

Year1 --> First table year field name take

Year2 --> Second table year field name take

Regards,