Select/Filter records in chart based on expression value

[#1]

Hello,

I'm wondering if it is possible to select records shown in a chart based on the value of an expression within that chart. In other words, I would like to have a user enter (in an input field I assume) a value then have the chart display only those records that the expression within the chart matches that value. For example, say I have a simple Sales, Cost, GM% chart like this:

Item Sales Cost GM%

12345 100 80 20%

55555 80 75 6.25%

98765 200 150 25%

GM% is an expression ([Sales] - [Cost]) / [Sales]). It can't be calculated in the script because Sales and Cost are sums of many records.

Say my input field has the value of 20. I would like to display only those records that GM% matches (in this case just the first record). Is there a way to accomplish something like this?

Thanks.

[Oleg_Troyansky]

Hi,

in a Straight table, you can define each expression as "Searcheable" (it's a check box in the Presentation tab). Unfortunately, the same functionality is not available for Pivot Tables.

If you really want to use an input box and a variable, you could filter using Set Analysis. If you examine the Help Section article on Set Analysis, the very last example describes something similar.

cheers,

Oleg

[oscar_ortiz]

Have a look at what I did in this QlikView document. This should work for what your trying to do.

Thanks

Oscar

[eiconsulting]

=if(((Sales-Cost)/Sales)=0.2,(Sales-Cost)/Sales)

You can easily replace the value with a variable. In my sample only the line with the value 20% appears.

If you need I can also test the variable.

Flavio

[#5]

Hi Oscar,

Your solution works as long as I don't have more than one record for the same dimension. Try adding another couple of records for the same items that already are in the table with different sales, cost & GM values and sum those expressions. The calculated dimension you added only filters the individual records where I need it to filter each dimension's totals. Unless there's another way to accomplish what you have done.

Too bad because I like this approach.

Thanks.

[#6]

Hi Flavio,

I tried it with the variable and it also works. I changed your expression to add "null()" if it doesn't meet the requirement and have "suppress null values" so the line won't show. The only problem (with the variable or not) is the total line. If my total line doens't meet my comparison requirement then it doesn't display and I want it to regardless.

[oscar_ortiz]

Sorry you can always wrap those statements with a sum(). See attached...

[#8]

You can use Dimensionality() to determine whether it is the total line or not. I know it works in a pivot. I think the total line would be Dimensionality() = 1, so you could try:

=If(Dimensionality() = 1, exp, if exp = variable then exp)

[#9]

It's actually Dimensionality() = 0 and that does work.

[#10]

Hi Oleg,

I think I like the searcheable feature the best. It gives the user the most flexibility. They can input values less than, greater than, within a range, etc. Just two questions on it:

1. I seem to have to clear my previous search prior to entering a new search value. is this the way it's intended to function or am I doing something wrong?

2. Is there a way to access the value put in the searchable field? I'd like to use it in my chart heading, for example, "All Sales with GM% " then the search value so it would look like this: "All Sales with GM% > 20".

Thanks.