How to filter data with (not)exists?
Hi all,
I'm trying to select customers that buy a certain product (sql equivalent of exists). And I want to display their total sales, not just those of that product. Similarly I want to search for customers that don't buy a certain product and display their sales.
How'd you best achieve that in a dataview or for the whole screen?
BR, Ray
Answers
-
Hi Ray Martens
How often will new products be added?
I'd say there's a few different ways, but one solution would be to use the screen select to limit the customers as required (maybe a user select on on product and a dynamic select on customer), and then apply a layout select to the dataview showing the sales cube to select all products (just make sure you get your to/keep correct). The screen will limit the customers showing but the layout select will show sales for all products.
The main issue with this is if new products are added the layout select will need to be manually updated.
2 -
if there was a product hierarchy with as top element a 'all products', wouldn't I be able to set the layout-select (with To)
and have the filtering done dynamically?
BR, Ray
3 -
Yes
0 -
Hi all,
so the 'exists' part is covered.
How would you do the 'not exists' part. I.e. search for all the customers that haven't bought a given product?
BR, Ray
0 -
Hi,
also this should be pretty easy.
Just select the product, create a Dataview with Customer by row and tag "show all", now you can use the filter to display only customers with an salesvalue of zero (or create an algortim block with b=if(abs(a)>0,1,0) and put the filter on this block [assuming that Sales block is block a ]
regards
Björn
2
