topic QlikView set analysis filter on all fields from one table in QlikView

QlikView set analysis filter on all fields from one table

roland74 — Thu, 04 Feb 2016 11:34:59 GMT

Hi all,

Does anyone know if it's possible to have set analysis react only on filters that are set on the fields of one table and disregard the filters on fields of all other tables?

I want to sum the field Quantity only if a filter is set on any field from the Calendar table.

I've found an example that does the opposite, it disregards the filters on all fields of the Calender table, but I can't find a way to turn it around.....

=sum({$<[$(=Concat({1<$Table={'Calendar'}>}distinct $Field,']=,[')&']=')>} Quantity)

Does anyone have a fix for this?

Thanx in advance!!

Re: QlikView set analysis filter on all fields from one table

sunny_talwar — Thu, 04 Feb 2016 17:40:06 GMT

Try this may be:

=Sum({1 - $<[$(=Concat({1<$Table={'Calendar'}>}distinct $Field,']=,[')&']=')>} Quantity)

Re: QlikView set analysis filter on all fields from one table

roland74 — Fri, 05 Feb 2016 08:34:59 GMT

Thank you for your suggestion,that almost dit the trick

The minus sign to invert the selection was the part I was missing and gave me the syntax I was looking for!

The correct syntax is:

=sum({$<[$(=Concat({1 - <$Table={'Calendar'}>}distinct $Field,']=,[')&']=')>} Quantity)

Do you btw have an idea how to add another table plus fields to this formula?

Re: QlikView set analysis filter on all fields from one table

sunny_talwar — Fri, 05 Feb 2016 17:55:50 GMT

What if you add the second table name to inside set analysis:

=Sum({$<[$(=Concat({1 - <$Table={'Calendar', 'TableName2'}>}distinct $Field,']=,[')&']=')>} Quantity)

Re: QlikView set analysis filter on all fields from one table

roland74 — Mon, 08 Feb 2016 08:15:00 GMT

Works great, thanks a lot!