Aggr and Only combination

[pe_rusnak]

Hi guys,

I really appreciate your help in the following. I have an filter pane with 3 values in Country field - ALL, USA, CA. What I would like to do in table/scatter/whatever in dimension is, that once the ALL is selected, please Qlik disregards the Country field and give the the whole dataset. Other hand please get the list based on current selection.

I have this one:

if(index(GetFieldSelections([Country]),'ALL',1)>0,aggr(Only({1<[Country]= >}[Name]),[Name]),aggr(Only([Name]),[Name]))

It should be pretty easy, but still does not work.

Thanks a lot!

PRU

[lironbaram]

hi 

assuming we talk about one selected value in the field 

than you can do like this 

if([Country]='ALL',aggr(Only({1<[Country]= >}[Name]),[Name]),aggr(Only([Name]),[Name]))

[pe_rusnak]

Hi,

thanks for the quick reply. But have you tested it? It does not work. And I would like to have a solution for multiple selection, therefore I used getfieldselection and index functions in condition...

If I select any of the filter, it does not work properly.

Using April 2020...

PRU

[Kushal_Chawda · Accepted solution]

instead of dimension, put the condition in measure of your chart

Assuming sum(sales) is your measure,

=if(GetFieldSelections(Country)='ALL', sum({<Country>}Sales),
Sum(Sales))

[pe_rusnak]

Hi @Kushal_Chawda ,

thanks for this tip. It is working. Is there any way how to achive the same with filter pane visualization? Display all of values if 1 is part of selection and particular values based if not?...Only function need to be used, but I think it is specific one from the aggregation group of function...at least it looks like or doing something incorrectly.

Regards,

PRU

[Kushal_Chawda]

I don't think you can do this in filter because filter automatically takes the AQL considerations and based on selection it will grey out the relevant filter values based on associations performed by AQL engine.

Probably as a work around you can create inline field in your data model for Name field by renaming it with different name(Name_all). Use that field as in a filter object and add that filter object in master visualization. Also create master visualization for another filter object in which you need to call original Name field. Add both filter object in container object. You can then use show-hide condition to display particular filter object.  When ALL is selected show filter object with inline Name field. Now only thing you need to setup is a field event trigger. When you select values from inline object you need to make a selection of those values in original Name field as well. Unfortunately field event trigger is not available in Qlik as of now but you can make use of some extensions(check Vizlib).

[pe_rusnak]

Thanks @Kushal_Chawda  for your explanation. But it does not have to be filter, but imagine that you would like to aply this logic to table dimension. It is different than filter and it does not work as well. Set analysis for Only function behaves differently than other sggregation functions.

PRU

[Kushal_Chawda]

In this case then you need to use the logic in measure instead as I mentioned earlier