Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

prefilter a dimension (and only allow the prefiltered selection)

hi, i need to use a few selection of a dimension Value, and fix it. example: i have a dimension "vendors" and i want to select an fix only 5 of them on a 30 list: vendors: A, B, C, D, E, F,......, Z, AA, AB I only want A, F, R, Y, AB I want to add this new dimension in a rapport. I don't want to use a filter after creation. Is it Possible? sorry for my wonderful Frenglish or englench.... best regards!
1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Re: prefilter a dimension (and only allow the prefiltered selection)

Hi Oliver,

I played around with your issue by mocking up a Vendors table and implementing a Selections table;  my Vendors table has only four columns: Code, Name, Group and Value; I added 10 dummy vendors with silly names, two groups (G1, G2) and funny values 🙂; I also created a variable vVendorList mimicking the list of vendors with special treatment. This is my script, out of this script perhaps the Selections table and the vVendorList variable as important to you, so keep an eye on them.

 

 

NoConcatenate
Vendors:
Load * Inline [
Code, Name, Group, Value
111,A,G1,123
112,B,G2,234
113,C,G1,555
114,D,G1,333
115,E,G2,222
116,F,G1,1123
117,G,G2,1234
118,H,G1,1555
119,I,G1,1333
120,J,G2,1222
];

NoConcatenate
Selections:
Load * Inline [
OptValue
All
Special
]; 

Set vVendorList=['111','115','118'];

 

 

The vendor list for this tests are 111, 115 and 118;  The Selections table has two options: All and Special; The test user interface is shown below:

Vendors-02.jpg

It has 5 panels:

  1. Options
  2. Selected Option
  3. Straight Table
  4. Table
  5. Graphics

The screenshot shows the UI when the Special selection is taken, e.g. the vendor list;  The Panel 3 with the Straight Table will not work for us, as you can see the Table on Panel 3 shows the vendors in the Vendors List variable, that is what you want to achieve. None of the vendors on the Vendors List belong to the group G2, so it is not showing; Panel 2 shows our current selection and Panel 1 is the selector.

Let's comment on the expressions behind each panel:

Panel 1: It is just the selector on the column =OptValue.

Panel 2: It is actually our current selection, its expression is:  =GetFieldSelections(OptValue)

Panel 3: It is the Straight table, it will never show us the results we want, anyhow, it has two expressions; The Code column expression is:

 

=if(GetFieldSelections(OptValue)='All', Code, if(match(Code,$(vVendorList))=0, Null, Code))

 

While the Sales column expression is:

 

=if(GetFieldSelections(OptValue)='All', Sum(Value), Sum( {$<Code={$(vVendorList)}>} Value))

 

I will explain these expressions later.

Panel 4: The Table, it also has two expressions,  exactly the same expression implemented into the Straight Table (Panel 3); Here, on the Code column we un-selected the option Include null values.

Panel 5: The Graphic shows Sales per Group for All or the Vendor List; We implemented this expression on the bar column:

 

=if(OptValue='All', Group, 'G1')

 

I am cheating here, because the Group selection is not driven by the Vendor List, this is a false positive.

The formulas:

  • We are using the function GetFieldSelections(OptValue) to tell apart when to use all the codes or just the list of the vendors.
  • We are also using this function match(Code,$(vVendorList)) it returns 0 (zero) when the Code is not in the Vendors List, a positive value otherwise.
  • We are also using this function Sum( {$<Code={$(vVendorList)}>} Value) at the Panel 4's Table on the sales column. It may not be required, I think we could use Sum(Value) with no dramas because the Code column is already suppressing nulls.

Please review my reply, writing it help me to sharp my Qlik skills, so I hope it helps you to manage your data as required.

Best regards,

Arnaldo Sandoval

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

11 Replies
Highlighted
Contributor III
Contributor III

Re: prefilter a dimension (and only allow the prefiltered selection)

try this
={$< [vendors]={A,F,R,Y,AB} >} [vendors]

 

Highlighted
Contributor II
Contributor II

Re: prefilter a dimension (and only allow the prefiltered selection)

hi,

thanks for the answer, it doesn't works.

Capture.JPG

 

any suggestion?

 

 

 

Highlighted
Specialist
Specialist

Re: prefilter a dimension (and only allow the prefiltered selection)

Hi Oliver

Can you try removing the [Code Vendeur] at the end of your expression!

Hope this helps,

Arnaldo Sandoval

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Highlighted
Contributor II
Contributor II

Re: prefilter a dimension (and only allow the prefiltered selection)

 Hi Arnaldo,

it doesn't works.

i still have the alert "garbage after the expression $"

i tried to create a new dimension, with the original table, but i don't have possibility to add the limitation.

perhaps because I don't use the right action?

 

Highlighted
Specialist
Specialist

Re: prefilter a dimension (and only allow the prefiltered selection)

Hi Oliver,

Are you counting Vendors? perhaps you should include a COUNT() or SUM() function, like this

=Count( {$<[Code Vendeur]={ ... } > } [Code Vendour] )

The code just count the [Code Vendour] for just the one on the list,  (I guess it should return 😎 but you probable want to SUM one of their measures, like sales, in that case, replace the second [Code Vendour] with the column you want to SUM.

Hope this helps,

Arnaldo Sandoval

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Highlighted
Contributor II
Contributor II

Re: prefilter a dimension (and only allow the prefiltered selection)

no, 

I don't want to count, I just want conserve a few list of vendors, and create a new dimension with them. I try to create this dimension with an existant table, named [vendors]

I tried your proposition but it doesn't work (gave "unvalid dimension")

the existant dimension is 

=[vendors] 

[vendors] has several items 

the new one is on the same table [vendors], but with a limited list of them. (take 10 of X thousand items)

 

I'm Sorry for my wonderful Frenglish! 😉

 

 

Highlighted
Specialist
Specialist

Re: prefilter a dimension (and only allow the prefiltered selection)

Hi Oliver,

I am working on a solution to your problem, I will get back to you in about two hours, as I have something to do in that time! actually I got it working but I want to polish it a bit.

Regards,

Arnaldo Sandoval

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Highlighted
Contributor II
Contributor II

Re: prefilter a dimension (and only allow the prefiltered selection)

thank you Arnaldo, take your time, no worry! 

 

Highlighted
Specialist
Specialist

Re: prefilter a dimension (and only allow the prefiltered selection)

Hi Oliver,

I played around with your issue by mocking up a Vendors table and implementing a Selections table;  my Vendors table has only four columns: Code, Name, Group and Value; I added 10 dummy vendors with silly names, two groups (G1, G2) and funny values 🙂; I also created a variable vVendorList mimicking the list of vendors with special treatment. This is my script, out of this script perhaps the Selections table and the vVendorList variable as important to you, so keep an eye on them.

 

 

NoConcatenate
Vendors:
Load * Inline [
Code, Name, Group, Value
111,A,G1,123
112,B,G2,234
113,C,G1,555
114,D,G1,333
115,E,G2,222
116,F,G1,1123
117,G,G2,1234
118,H,G1,1555
119,I,G1,1333
120,J,G2,1222
];

NoConcatenate
Selections:
Load * Inline [
OptValue
All
Special
]; 

Set vVendorList=['111','115','118'];

 

 

The vendor list for this tests are 111, 115 and 118;  The Selections table has two options: All and Special; The test user interface is shown below:

Vendors-02.jpg

It has 5 panels:

  1. Options
  2. Selected Option
  3. Straight Table
  4. Table
  5. Graphics

The screenshot shows the UI when the Special selection is taken, e.g. the vendor list;  The Panel 3 with the Straight Table will not work for us, as you can see the Table on Panel 3 shows the vendors in the Vendors List variable, that is what you want to achieve. None of the vendors on the Vendors List belong to the group G2, so it is not showing; Panel 2 shows our current selection and Panel 1 is the selector.

Let's comment on the expressions behind each panel:

Panel 1: It is just the selector on the column =OptValue.

Panel 2: It is actually our current selection, its expression is:  =GetFieldSelections(OptValue)

Panel 3: It is the Straight table, it will never show us the results we want, anyhow, it has two expressions; The Code column expression is:

 

=if(GetFieldSelections(OptValue)='All', Code, if(match(Code,$(vVendorList))=0, Null, Code))

 

While the Sales column expression is:

 

=if(GetFieldSelections(OptValue)='All', Sum(Value), Sum( {$<Code={$(vVendorList)}>} Value))

 

I will explain these expressions later.

Panel 4: The Table, it also has two expressions,  exactly the same expression implemented into the Straight Table (Panel 3); Here, on the Code column we un-selected the option Include null values.

Panel 5: The Graphic shows Sales per Group for All or the Vendor List; We implemented this expression on the bar column:

 

=if(OptValue='All', Group, 'G1')

 

I am cheating here, because the Group selection is not driven by the Vendor List, this is a false positive.

The formulas:

  • We are using the function GetFieldSelections(OptValue) to tell apart when to use all the codes or just the list of the vendors.
  • We are also using this function match(Code,$(vVendorList)) it returns 0 (zero) when the Code is not in the Vendors List, a positive value otherwise.
  • We are also using this function Sum( {$<Code={$(vVendorList)}>} Value) at the Panel 4's Table on the sales column. It may not be required, I think we could use Sum(Value) with no dramas because the Code column is already suppressing nulls.

Please review my reply, writing it help me to sharp my Qlik skills, so I hope it helps you to manage your data as required.

Best regards,

Arnaldo Sandoval

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post