topic Re: Input Box triggers Expression filter in QlikView

Input Box triggers Expression filter

Anonymous — Fri, 06 Dec 2013 19:22:22 GMT

Guys, I'm having an issue with something that seems simple.

I need to set a filter on a dimension, say "Area" in the attached example, based upon an input field that corresponds to an expression...Sum(Revenue). As I need to filter upon an aggregate, I can't just look to the base-level "Revenue" field.

Since I want the input box to change the set of available Areas on other tabs, it's not enough to just put an IF statement in the chart to hide the rows that are below the threshhold.

While a search box (on the expression) or a slider seem to work, what am I doing wrong that's preventing the input box from using the trigger to do the same thing?

With thanks,

Frank

Re: Input Box triggers Expression filter

jerem1234 — Fri, 06 Dec 2013 20:44:35 GMT

Please find attached to see its working how you want. When using select in field, the field value cant be a function, can only be a field name. So had to create an expression in the search string to get the required values. The formula I used was:

='(' & Concat(if(aggr(sum(Revenue),Area)> vMinAggregatedRevenue, Area), '|') & ')'

in the search string section of trigger.

Hope this helps!

Re: Input Box triggers Expression filter

Anonymous — Fri, 06 Dec 2013 21:09:11 GMT

Perfect...many thanks!

Re: Input Box triggers Expression filter

phcaptjim — Tue, 14 Jan 2014 16:41:08 GMT

This works well, except for one minor detail that I have been unable to resolve.

If you input revenue, the selection works perfectly. If you increase the value you originally input, the selection again works well. However, if you first input 40, you get the correct selections, but if you now enter 10, you do not get the proper selections. it's only checking the currently selected instead of checking all values. I have tried clearing the field using a trigger but that is not working.

Does anyone have any solutions? It seems it should be a simple fix.

Thanks!

Re: Input Box triggers Expression filter

jerem1234 — Tue, 14 Jan 2014 17:19:06 GMT

Yep, like you said it goes based on selected values. So when you have the selected values, the concat is reduced to what is selected. Therefore you need to ignore selections, so you have to add in 1's to set analysis. To edit the original equation, it would be:

='(' & Concat({1}if(aggr(sum({1}Revenue),Area)> vMinAggregatedRevenue, Area), '|') & ')'

Make sure both concat AND sum get the 1.

Hope this helps!

Re: Input Box triggers Expression filter

phcaptjim — Tue, 14 Jan 2014 17:24:33 GMT

Thanks! I added the {1} but only after the SUM and not after the CONCAT function. Ooops. Works great now!