topic Re: Filtering based on custom condition in QlikView

Filtering based on custom condition

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all

here my requirement is to show when i select yes in flag filter it should show total cost in 2015 year(241 dollars)

for instance :

if i select YES it should show total five records(records with yes and no flag)

if i select NO it should show only records with no flag

Please check the Attachment for details

swuehl‌stevedark‌

Re: Filtering based on custom condition

swuehl — Thu, 09 Feb 2017 08:42:17 GMT

Try as expression

=Sum ({<Flag += {'No'}>}TotalCost)

Re: Filtering based on custom condition

Thu, 09 Feb 2017 08:43:09 GMT

pls have a look

Re: Filtering based on custom condition

stevedark — Thu, 09 Feb 2017 08:47:31 GMT

I like to solve these kind of things in the data model, if you add this table and use Flag2 it will work:

LOAD

Flag,Flag2

INLINE [

Flag,Flag2

No,Yes

Yes,Yes

Yes,No

];

You can then choose to rename fields, so Flag2 is called Flag, and you can even use a HidePrefix so that the join key is not visible.

Stefan's set analysis based answer will also work for you.

Steve

Re: Filtering based on custom condition

Anonymous — Thu, 09 Feb 2017 09:52:17 GMT

yes its working fine with total cost but my requirement need to show all records in table also(when select yes it should show all records in table )

Select YES it should show all records in table

select NO it should show only records with NO flag

Re: Filtering based on custom condition

swuehl — Thu, 09 Feb 2017 10:11:59 GMT

If you generally need to show all records with flag Yes or No when selecting in listbox 'Yes', then go with the script solution suggested by Steve Dark.

If you need to show a table with all records, but it is on the same page / chart also required to filter on records using the specific selected value, then you might want to consider a set analysis solution.

You can also combine both solutions.

For example, to show all records, create a straight table with the all the fields you want to show as dimensions, and use an expression (that you can hide on presentation tab) like

=Count ({<Flag += {'No'}>}TotalCost)

Re: Filtering based on custom condition

gowtham_patnaik — Thu, 09 Feb 2017 10:15:42 GMT

try this:

=if(Flag='Yes',sum({1}TotalCost),sum({$<flag={'No'}>}TotalCost))

Re: Filtering based on custom condition

stevedark — Thu, 09 Feb 2017 10:22:57 GMT

The script solution is generally good, but you have to be *really* careful if you are putting the dimension in a table. The way that one dim links to two will cause duplication of rows (if you put Flag2 in the table after usuing my script).

If you only use Flag2 in listboxes and Flag in tables, then everything will work fine.

Set analysis allows you to be more prescriptive about what happens where.

Steve

Re: Filtering based on custom condition

Anonymous — Thu, 09 Feb 2017 11:20:49 GMT

yeah steve its working fine i used Flag2 as in filter and flag used in chart expressions