Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How to filter table data based on values of backend fields

Hello,

I did load the following table on QlikSense. It contains information about people (People, Gender, Country, Place and Age). Done easily

question 1.JPG

Then I did create a new QlikSense Sheet with a table that only display the fields People, Country and Age. Done easily.

question2.JPG

Now I would like to only display People based on some filtering on the hidden fields that are not displayed on my table (gender and Place).

Indeed I would like to only display people where gender is f and Place is House.

So I would like to know if it is possible ? If yes what is the formula that I have to use and where to write it ? I guess that I have to write it in the add-ons (complements in french) section.

Thanks in advance for your help.

Fred

1 Solution

Accepted Solutions
darkhorse
Valued Contributor

Re: How to filter table data based on values of backend fields

Hello,

Try to it as a measure, not dimension. Add Column--> Measure.

BR

Serhan

10 Replies
darkhorse
Valued Contributor

Re: How to filter table data based on values of backend fields

Hello Fred,

Replace Age column with the following formula.

Avg({<Gender={'f'}, Place={'House'}>}Age)

It's called Set Analysis. Try help for more detail.

BR

Serhan

brunobertels
Valued Contributor

Re: How to filter table data based on values of backend fields

Bonjour

Bienvenu dans le monde merveilleux des Set Analysis , formule permettant de filtrer suivant d'autres valeurs , inclus dans d'autres champs , affichées ou non dans ton tableau...

voici un lien d'un outil trés utile pour construire ce type de formule :

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

Dans ton tableau tu veux présenter l'age d'un population que pour les femme en maison

se serait quelque chose comme çà :

Max({<Gender={'f'}, Place={'House'}>}Age)

mais çà doit marcher aussi avec Avg

Et il faut décocher la case "afficher les valeurs nuls" dans tes dimensions

Bruno

PS : tu peux essayer aussi d'installer sur le site qlij branch l'extension Mesure builder qui permet de construire et tester tes formules,

Not applicable

Re: How to filter table data based on values of backend fields

Hi Serhan,

Actually I gave it  a try but it didn't work ??

Any idea why ?

Regards,

Fredquestion3.JPG

darkhorse
Valued Contributor

Re: How to filter table data based on values of backend fields

Hello,

Try to it as a measure, not dimension. Add Column--> Measure.

BR

Serhan

Not applicable

Re: How to filter table data based on values of backend fields

Ok !!!

Now it works like a charm.

But I was wondering : If I had in my table only the two dimensions people and country would it be possible to do the filtering without the age Measure ?

question4.JPG

Thanks,

Fred

darkhorse
Valued Contributor

Re: How to filter table data based on values of backend fields

Hello,

Yes, it would work again with some tricks.

You need to fake one of your dimensions as a measure such as with a combination of only() and aggr() functions.

Create a list box of House with an expression like aggr(only({<Gender={'f'}, Place={'House'}>}House),House) . You will understand the behaviour.

BR

Serhan

Not applicable

Re: How to filter table data based on values of backend fields

Merci beaucoup pour ta réponse qui marche correctement.

Serait aurait-il été possible si j'avais voulu afficher une table avec uniquement les dimensions people et country et sans une colonne avec un mesure comme l'âge ?

question4.JPG

brunobertels
Valued Contributor

Re: How to filter table data based on values of backend fields

Bonjour

L'idée de Serhan ma semble bien pour faire ce que tu souhaites. Après tu peux peut etre creuser d'autres solutions :

- créer des dimensions calculées dans ton script pour n'avoir que les champs restreint en fonction de ce champs calculé.

- utiliser des variables ( qsvariable) a télécharger sur Qlikbranch ou directement avec le module variable de QlikSense

- utiliser des dimensions cycliques ( moins sur de cette solution, comme toi je débute sur QlikSense)

Je te conseille de poster un exemple de ta base avec tes demandes , ainsi il sera plus facile de t'aider en travaillant sur un fichier .qvf qu'on peut s'échanger.

Bruno

PS : Bon courage

Not applicable

Re: How to filter table data based on values of backend fields

Sorry to bother you with my newbie question but could you give me some step by step details to perform that cause it is definitely what I am looking for but I don't know how to implement that :-(

Community Browser