App Development

[qlikuser22]

Hi 

I am trying to write a set expression for a filter pane in qliksense. As I am new to this, finding it really hard. Please any help will be appreciated. 

I have a data consisting of Store ID , list of products and Sales. I wrote an expression for finding the total sales for the store ID starting with AC and for products furniture and electronics. 

Now I want the same to be used in a filter pane which displays all the store ID starting with AC* and also the store IDs which belongs to the product furniture-sofa and electronics-mobile. 

The expression I tried is

Sum({<[Store ID] = {"AC*"}+P({<PRODUCT={'Furniture-Sofa,'Electronics-Mobile'}>}[Store ])>} [Sales])

The output of Sum expression is , it displays all details with AC* and Furniture -Sofa and Electronics - Mobile. 

Now I want to have a separate filter pane for Store ID with the same condition. 

Expected output:

Thanks a lot in advance.

[chris_djih]

please excuse my unconventional suggestion:
But in my opinion you don't need a filter pane. Since qlik has the power, that you can interact with the charts and can filter by interacting with them. So my way of designing apps is to encourage users to use these great functionalities widely. And since most of them came from other tools where they have to use a filter pane.
The only way to empower user to use the full potential of qlik is to force them by not providing a filter pane, when the selection can be done easily within the chart itself.

If you still urgently need the filter pane, I will try to think of a solution 🙂

[qlikuser22]

Hi @chris_djih 

Thanks for the help. But it would be great if I can get some help in writing expression for the same in filter pane. Thanks in advance. 

[chris_djih · Accepted solution]

OK, then let me translate your set expression into the dimension expression:
{<[Store ID] = {"AC*"}+P({<PRODUCT={'Furniture-Sofa,'Electronics-Mobile'}>}[Store ])>}
If( left([Store ID],2)='AC' orMatch([PRODUCT],'Furniture-Sofa,'Electronics-Mobile'), [Store ID])

remark: Since in  dimension you can't use aggregations or set-analysis, you have to use If.

[qlikuser22]

Hi @chris_djih 

Thanks a lot. It saved my life. I have one more doubt in sum expression.

Sum({<[Store ID] = {"AC*","SA*"}+P({<PRODUCT={'Furniture-Sofa,'Electronics-Mobile'}, Amount = {'Positive'}>}[Store ])>} [Sales]) 

I have a dimension called Amount with two values as Positive and negative. If I add this in my Sum expression, I get the output as 0. Any help? I am sure it will work somehow, I don't know if my syntax is wrong. 

Thanks a lot. 🙂

[chris_djih]

it depends how your data model looks like and what you want to pre filter.
in a blind guess i would suggest to place this filter after the P() function. Because you wnat positive Sales, not positive StoreIDs?

Sum({<[Store ID] = {"AC*","SA*"}+P({<PRODUCT={'Furniture-Sofa,'Electronics-Mobile'}>}[Store ]), Amount = {'Positive'}>} [Sales]) 

[qlikuser22]

Hi @chris_djih 

Thanks. If I select positive filter, I need all their corresponding store ids and other data in the table. 

Will try the expression you mentioned as well. Thanks a lot.