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

Find more posts tagged with

Usage

Accepted answers

All comments

Brendan Broughton

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.

Previous Member

Hi Brendan Broughton,

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

Previous Member

Yes

Previous Member

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

Björn Reuber

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