How to filter data with (not)exists?

Options
Unknown
Unknown
in Platform

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

Tagged:

Answers

  • Brendan Broughton
    Brendan Broughton Active Partner
    Level 200: Leveraging Board for Business Insights Level 100: Foundations of Building in Board First Anniversary First Comment
    Options

    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
    Previous Member
    5 Up Votes First Comment 5 Likes First Anniversary
    edited March 2020
    Options

    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)

    image

    and have the filtering done dynamically?

     

    BR, Ray

  • Previous Member
    Previous Member
    5 Up Votes First Comment 5 Likes First Anniversary
    Options

    Yes

  • Previous Member
    Previous Member
    5 Up Votes First Comment 5 Likes First Anniversary
    Options

    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
    Björn Reuber Employee
    Community Captain First Answer First Anniversary 5 Up Votes
    Options

    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

Categories