topic Re: Wildmatch on combo chart (Filter multiple selection) in QlikView

Wildmatch on combo chart (Filter multiple selection)

demcug82 — Thu, 24 Aug 2017 15:08:02 GMT

Hi All,

I have a Filter (COUNTRY) where i can select USA, Canada, Mexico e.g.

In an expression of combo chart, if i have selected USA or USA and Canada or USA and Mexico or USA , Mexico and Canada, or anything i have to use Expression A otherwise Expression B using (IF)

I Create this:

if(

wildmatch(concat(COUNTRY,''),'*USA*'),

sum(X),

sum(Y)

)

but works only if I do not select anything (sum X) or if not select USA (sum Y).

When i select USA use sum Y.

If i insert this expression on a label works perfectly.

I have tried also substringcount(concat(COUNTRY),'USA')=1 but don't works.

Any Idea?

Thanks and Regads,

Demetrio

Re: Wildmatch on combo chart (Filter multiple selection)

sunny_talwar — Thu, 24 Aug 2017 15:15:09 GMT

May be try this

SubStringCount(Concat(DISTINCT COUNTRY), 'USA') >= 1

Re: Wildmatch on combo chart (Filter multiple selection)

sunny_talwar — Thu, 24 Aug 2017 15:15:57 GMT

Or this

If(WildMatch(Concat(DISTINCT COUNTRY,''),'*USA*'), Sum(X), Sum(Y))

Re: Wildmatch on combo chart (Filter multiple selection)

demcug82 — Thu, 24 Aug 2017 15:50:26 GMT

Hi Sunny,

don't works.

The problem it's that in the chart the first condition is false when USA is selected ....in the text object not....and works...

Re: Wildmatch on combo chart (Filter multiple selection)

sunny_talwar — Thu, 24 Aug 2017 15:52:36 GMT

Would you be able to share a sample to show the issue?

Re: Wildmatch on combo chart (Filter multiple selection)

demcug82 — Thu, 24 Aug 2017 15:53:02 GMT

and if use getfieldselection(country) and select only USA works ...

Re: Wildmatch on combo chart (Filter multiple selection)

demcug82 — Fri, 25 Aug 2017 08:01:58 GMT

Hi Sunny,

in attachment a example.

The goal is that if USA is selected (or anything) we have sum(x) otherywise sum(y).

In example sum(X) 100 for all country

thanks

Demetrio

Re: Wildmatch on combo chart (Filter multiple selection)

sunny_talwar — Fri, 25 Aug 2017 09:53:02 GMT

May be this

=If(WildMatch(GetFieldSelections(Country), '*USA*'), Sum({<Country>}X), Sum({<Country>}Y))

When USA is selected

When USA is not selected

When Multiple things are selected

Re: Wildmatch on combo chart (Filter multiple selection)

demcug82 — Fri, 25 Aug 2017 15:09:29 GMT

Hey Sunny,

don't works yet but i don't wrote all .

In the filter i have others 3 country that have to see only on chart (not selectable on filter) when USA is selected.

For this on the script i have duplicate field country, named country_filter that write null for this country and used on filter.

That expression used it's :

if(

wildmatch(GetFieldSelections(F_ECONOMICS_NAFTA.COUNTRY),'*USA*'),

// EXPRESSION A

sum({<F_ECONOMICS_NAFTA.COUNTRY_FILTER=,

F_ECONOMICS_NAFTA.COUNTRY={$(=v_country_exchange_rate_NAFTA2),$(=v_country_exchange_rate_NAFTA)},

F_ECONOMICS_NAFTA.DATA ={">=$(=DATE#((v_selected_period5)&'-01'))<=$(=DATE#((v_selected_period6)&'-01'))"}>}

F_ECONOMICS_NAFTA.AVERAGE_RATE_FF_MONTHLY),

// EXPRESSION B

sum({<F_ECONOMICS_NAFTA.DATA ={">=$(=DATE#((v_selected_period5)&'-01'))<=$(=DATE#((v_selected_period6)&'-01'))"}>}

F_ECONOMICS_NAFTA.AVERAGE_RATE_FF_MONTHLY)

)

____________

where

v_country_exchange_rate_NAFTA2

=chr(39)&Concat(F_ECONOMICS_NAFTA.COUNTRY_FILTER,chr(39)&','&chr(39))&chr(39)

v_country_exchange_rate_NAFTA = 'Euro Area','Japan','UK'

______________

This don't works only when USA is select

Where i have to modify it?

Re: Wildmatch on combo chart (Filter multiple selection)

sunny_talwar — Fri, 25 Aug 2017 15:14:38 GMT

May be try this

If(WildMatch(GetFieldSelections(F_ECONOMICS_NAFTA.COUNTRY), '*USA*'),

// EXPRESSION A

Sum({<F_ECONOMICS_NAFTA.COUNTRY_FILTER, F_ECONOMICS_NAFTA.COUNTRY =P(F_ECONOMICS_NAFTA.COUNTRY_FILTER) + {$(v_country_exchange_rate_NAFTA)},

F_ECONOMICS_NAFTA.DATA = {">=$(=Date#((v_selected_period5) & '-01'))<=$(=Date#((v_selected_period6) & '-01'))"}>} F_ECONOMICS_NAFTA.AVERAGE_RATE_FF_MONTHLY),

// EXPRESSION B

Sum({<F_ECONOMICS_NAFTA.DATA = {">=$(=Date#((v_selected_period5) & '-01'))<=$(=Date#((v_selected_period6) & '-01'))"}>} F_ECONOMICS_NAFTA.AVERAGE_RATE_FF_MONTHLY))

Re: Wildmatch on combo chart (Filter multiple selection)

demcug82 — Fri, 25 Aug 2017 15:27:25 GMT

not yet... but

I have reversed the cases and now don't works only when mexico and canada are selected togheter.

perhaps.. with another if ....getcount..

if(

GetFieldSelections(F_ECONOMICS_NAFTA.COUNTRY_FILTER)='Canada' or GetFieldSelections(F_ECONOMICS_NAFTA.COUNTRY_FILTER)='Mexico',

sum({<F_ECONOMICS_NAFTA.DATA ={">=$(=DATE#((v_selected_period5)&'-01'))<=$(=DATE#((v_selected_period6)&'-01'))"}>}

F_ECONOMICS_NAFTA.AVERAGE_RATE_FF_MONTHLY),

sum({<

F_ECONOMICS_NAFTA.COUNTRY_FILTER=,

//F_ECONOMICS_NAFTA.COUNTRY={'Euro Area','Japan','UK',$(=v_country_exchange_rate_NAFTA)},

F_ECONOMICS_NAFTA.COUNTRY={$(=v_country_exchange_rate_NAFTA2),$(=v_country_exchange_rate_NAFTA)},

F_ECONOMICS_NAFTA.DATA ={">=$(=DATE#((v_selected_period5)&'-01'))<=$(=DATE#((v_selected_period6)&'-01'))"}>}

F_ECONOMICS_NAFTA.AVERAGE_RATE_FF_MONTHLY))

Re: Wildmatch on combo chart (Filter multiple selection)

sunny_talwar — Fri, 25 Aug 2017 15:35:33 GMT

Not sure....unless you can share an example... it might be too difficult to suggest anything

Re: Wildmatch on combo chart (Filter multiple selection)

demcug82 — Wed, 30 Aug 2017 09:47:18 GMT

Resolved

I have created a variable: =substringcount(concat(F_ECONOMICS_NAFTA.COUNTRY_FILTER),'USA') and inserted this on expression (>0)

Unless variable the chart don't works fine but with this it's ok!