topic Re: Function / Method to Filter a list of Value in a dimension in Straight table in QlikView

Function / Method to Filter a list of Value in a dimension in Straight table

Mon, 23 Sep 2013 16:00:34 GMT

Is there a way to to Hide / Filter a specific Value in the straight table dimension.

Ex:

Country

USA

France

Canada

Japan

and I want to filter the Value say (Japan) from the straight table.

Best -

Re: Function / Method to Filter a list of Value in a dimension in Straight table

manas_bn — Mon, 23 Sep 2013 18:56:23 GMT

Hello,

A crude method I can think of:

Add a calculated dimension

=if(not match(Country,'Japan'),Country)

Suppress Null values in dimensions

Cheers!

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Mon, 23 Sep 2013 18:58:51 GMT

Try this for your dimension.

if(Country<>Japan,Country)

Re: Function / Method to Filter a list of Value in a dimension in Straight table

jagan — Tue, 24 Sep 2013 09:22:21 GMT

Hi,

Instead of going for Calculated Dimension since Calculated Dimension leads to performance issues, you can do the same in Expression like this

=Sum({<Country-={'Japan'}>} MeasureName)

Hope this helps you.

Regards,

Jagan.

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Tue, 24 Sep 2013 13:01:05 GMT

you can use calculated dimention :if(country<>'japan',country).

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Tue, 24 Sep 2013 20:22:49 GMT

Hi Jagan -

I am trying the exact Expression, It works for "=",, But doesn't work for "<>".

Any Idea for NOT EQUAL Sign in to define Here.

Example :

=Sum({$<Country = {'Japan','France'}>}Revenue) ----- Works

=Sum({$<Country <> {'Japan','France'}>}Revenue) -------- Doesn't Work

=Sum({$<Country -= {'Japan','France'}>}Revenue) -------- Doesn't Work

=Sum({$<Country != {'Japan','France'}>}Revenue) -------- Doesn't Work

Is there a specific Syntax for NOT EQUAL in QV Set Analysis

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Tue, 24 Sep 2013 20:27:22 GMT

Thanks -

This Works!

=IF(Country<>'Japan',Country) and I have suppressed the Null Values.

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Tue, 24 Sep 2013 20:29:37 GMT

Thanks -

This Works,

The Code Below:

=If(NOT MATCH(Country,'Japan','France'),Country)

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Tue, 24 Sep 2013 20:31:55 GMT

=If(NOT MATCH(Country,'Japan','France'),Country) ---- This Works

Re: Function / Method to Filter a list of Value in a dimension in Straight table

jagan — Wed, 25 Sep 2013 01:31:16 GMT

Hi Syed,

This would definitely work, Qlikview shows as error (underlined red color) but it works. Please check.

=Sum({<Country -= {'Japan','France'}>}Revenue)

Regards,

Jagan.

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Wed, 25 Sep 2013 05:23:10 GMT

Hi,

If you got correct answer then mark it as correct and close the discussion

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Fri, 27 Sep 2013 16:52:24 GMT

Thanks Jagan

Sorry for the delayed response,, It works Excellent.

Thanks -

Re: Function / Method to Filter a list of Value in a dimension in Straight table

jagan — Fri, 27 Sep 2013 17:31:55 GMT

Hi Syed,

Please close the disucssion by giving Correct and Help answers to the posts. So that it helps others to find solutions to the similar type of requirements.

Regards,

Jagan.

Re: Function / Method to Filter a list of Value in a dimension in Straight table

Fri, 27 Sep 2013 21:04:56 GMT

Correct Answer

Re: Function / Method to Filter a list of Value in a dimension in Straight table

jagan — Sat, 28 Sep 2013 06:56:32 GMT

Hi Syed,

Click the Correct and Helpful Answer buttons in the posts which gives the solution.

Regards,

Jagan.

Re: Function / Method to Filter a list of Value in a dimension in Straight table

rafaeljmvicente — Mon, 30 Jul 2018 12:28:55 GMT

Thanks, it worked.

For more complex formulas

=Sum({$<Region={'APAC'}>} TeoFDCost ) / Sum({$<Region={'APAC'}>} (TeoFDCost-FCVCostBW) )