Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Variable as Dimension for Filtering Table

Greetings All,

This is my first post given that most of the times I'm able to find a solution to my problems by simply searching the forum.  In any event, I have not been able to locate a solution to my problem.

The Problem:

I've created a straight table that contains a calculated expression based on certain field criteria being met.  Depending on the criteria the expression will return Positive, Negative, or will be blank if it doesn't match any of the criteria set forth in the expression.  The expression itself has been created as a variable so that I can capture the responses for future use.  Since the straight table has many rows I would like the ability to filter the table based on the values being captured by the variable (Positive or Negative, ignoring blanks), what I've done was simply create a dimension based on the variable, which then allows me to filter based on the results.  At first glance it appears to work but when I take a closer look not all of the results are being returned, in other words I count a total of 25 that say Positive but when I filter using the dimension it only returns 10 of the 25; furthermore, I see that the current selections box is selecting all the fields that are being used to calculate the variable. 

Can anyone provide a suggestion that would allow me to filter the results so that it only shows the relevant results, in other words the ablility to select from a list box 'Positive or Negative' and have it return all the proper results.  Thanks in advance for any help or suggestions you may be able to provide. 

6 Replies
mov
Esteemed Contributor III

Re: Variable as Dimension for Filtering Table

There is a painless way of selecting by expression result.  No dimensions needed.  In your straight table, open properties, go to the Presentation tab, select expression, and check "Searchable".
After this you can search by this expression.

Regards,
Michael

Not applicable

Re: Variable as Dimension for Filtering Table

Michael,

Thanks for the response, this does provide the ability to filter; however, once I filter for one result ('Positive'), the user would then need to clear the results to filer for the other ('Negative).  The ideal solution would allow similar flexibility to a dimension whereby one can select Positive and see the corresponding results and then select Negative to see the same.  Any other thoughts would be greatly appreciated. 

Best Regards,

Dan.

mov
Esteemed Contributor III

Re: Variable as Dimension for Filtering Table

Dan,

The fact is that the result Positive/Negative is dynamic, that is - depends on selections in other fields.  What you get maybe is not convenient but it is correct, I think.

The only alternative I can tink of at this time: create a calculated list box, based on the same expresiion which you use for "Negative / Positive", probably aggreagted by some fields.  The fields used in aggregation will be the ones actually get selected.

Regards,
Michael

Not applicable

Re: Variable as Dimension for Filtering Table

Michael,

When you say calculated list box, do you mean using variable in a list box?  Again, the variable is doing the calculation.  If so, that is what I've done and the issue I'm having is that it doesn't return all the results and I'm not sure why.  It's doing what you've mentioned where it selects all the fields used in the aggregration but doesn't return all the results. 

mov
Esteemed Contributor III

Re: Variable as Dimension for Filtering Table

Dan,

About the calculated List box:
Start creating new List box.  In the Field drop-down, find <Expression> and click on it.  Write an expression.  It may be your variable, depending on the variable form, or just the same expression as in the variable, and aggregated by something.  Probably aggregated by multiple fields - depending on how you want to define the values.
If you get it correctly, the values in your list box will be Negative/Positive.  The selection result depends on the fields you use in aggregations.
This is not "better" or "worse" than serach by expression result - it is pretty much the same.  The major difference is that you have more flexibility with the aggregations - hence, with the results.

If it doesn't help, search "calculated list box".

Regards,
Michael

Not applicable

Re: Variable as Dimension for Filtering Table

Michael,

I've done something similar.  I'll revisit what I did and modify accordingly.  Hopefully, that will resolved issue. Thanks again for your help. 

Community Browser