topic Re: Filter displayed data on table based on Input Box in QlikView

Filter displayed data on table based on Input Box

Anonymous — Thu, 19 Jun 2014 12:10:28 GMT

Hi Guys,

For sure it is simple but I cannot make it.

I want to be able to show only the customers which total cost (the cost per Customer is a sum of costs of many transactions) is greater than (or below) a number that is supplied in an InputBox.

I created a Set analysis expression:

SUM( { < Cost = {"$(vInput)"} > } Cost)

The problem is it evaluates the Cost at row level (which is transaction) and I want the evaluation to be done on Customer level. I.e. if I enter >8000 in the input box I got a sum of all transactions which individually exceeds 8000 rather than only the customers which Total Cost (sum of all transactions) exceeds 8000.

So if a customer has 2 transactions - 5000 and 4000 it wont be displayed as each transaction is below 8000, and it should be as the total is.

I included a file and a picture to be more clear.

I tired SUM and AGGR on Customer but with no success...

Thanks in advance,

Alex

Re: Filter displayed data on table based on Input Box

Gysbert_Wassenaar — Thu, 19 Jun 2014 12:44:56 GMT

Try sum( { < Customer = {"=sum(Cost) $(vInput)"} > } Cost)

Re: Filter displayed data on table based on Input Box

Thu, 19 Jun 2014 13:09:28 GMT

Try this code

sum( { < Cost ={">= $(vInput)"} > } Cost)

Be sure that don't give Greater than symbol in input box just give 8000.

Regards,

Kumari.

Re: Filter displayed data on table based on Input Box

Thu, 19 Jun 2014 13:33:24 GMT

Use: if(sum(Cost)$(vInput),sum(Cost),0)

It works. I just did that in the file you gave and attached it for your reference.

Re: Filter displayed data on table based on Input Box

Thu, 19 Jun 2014 13:56:16 GMT

If you use > in the Input box then try this code

sum( { < Cost ={"$(vInput)"} > } Cost)

Re: Filter displayed data on table based on Input Box

Anonymous — Fri, 20 Jun 2014 10:34:04 GMT

Thanks to Gysbert, Preethi and all the folks who helped, appreciate it! Especially for the fast replies!

Re: Filter displayed data on table based on Input Box

Anonymous — Fri, 20 Jun 2014 11:52:14 GMT

Ha... no set analysis... just IF, very nice, did not think about that at all, thanks!