ignore all selections apart from 1 field

[hopkinsc]

Hi All,

I am trying to get some set analysis syntax to ignore all selections on fields apart from 1 specific field.

Can anyone help please?

I know using the '1' set modifier will ignore all current selections, but how do i tell it to include selections made on a specific field?

Sum{1<>}Sales

[#2]

Hi,

Sales = $::Sales is not excluded.

Sum({1<YearMonth = $::YearMonth >} Price)

Rebeca

[israrkhan]

Hi Rebeca,

i saw this syntax first time in qlikview, and just now i tried,

but its not working with me, its show like unknown syntax in qlikview,

can you please share any example, where this syntax in working...

Thank You...

Khan

[stevedark]

Hi David,

The Concat statement will still be impacted by selections in other fields.

I believe the required syntax is this:

=sum({1<FieldName={$(=if(isnull(GetFieldSelections(FieldName)), '*', chr(39) & GetFieldSelections(FieldName, chr(39) & ', ' & chr(39)) & chr(39)))}>}Value)

This forces the selections on the one field into the current selections.  If you paste the code inside the dollarsign expansion into a text box you will see how it is working.  The isnull is required to make it work when there are no selections on the Field.

The route of listing all fields with an equals sign might be more efficient at run time.  If you could build a variable in the load script that had every field in it by enumerating around the fields collection and appending to a list that that would probably do the trick.

Hope that helps.

Steve

[dvasseur]

The right syntax is Sum({1<YearMonth = $::YearMonth >} Price) and indeed it seems OK, never thought of this !

[rustyfishbones]

See this

http://community.qlik.com/message/394109#394109

[dvasseur]

Hi Steve,

You're right regarding the IsNull() check. However, I do prefer Concat() rather than GetFieldSelections(), Concat() is not limiting the number of values (check 3rd param of GetFieldSelections()) and you can write Concat(Chr(39) & [field] & Chr(39)), much better than all those Chr(39) with GetFieldSelections.

Both solutions should be improved to handle quotes in a field value BTW 😉

David

[#8]

Hi,

Follows suit. Only had changed the syntax is not $$: is $::

[#9 · Accepted solution]

Hi,

Try

Sum({1<YEAR = $::YEAR , MONTH=$::MONTH>} PRICE)

Rebeca

[israrkhan]

Yes Rebeca, Its working ,  but i was trying with $$:

Thank You...

[jchoucq]

Hi,

what about sum({1<YEAR=p(YEAR)> }price)

regards,

johann