Filter displayed data on table based on Input Box

[Anonymous]

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

[Gysbert_Wassenaar · Accepted solution]

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

[#3]

Try this code

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

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

Regards,

Kumari.

[#4]

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.

[#5]

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

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

[Anonymous]

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

[Anonymous]

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