Customers who have bought Product A but not Product B

[pkelly]

Against our Sales Invoice table we have a field called product family.

For example, Sawn, Machined, Flooring, Panels etc...

One of the questions we would like to ask is "Show me customers who have bought Sawn but not Machined" or "Show me customers who have bought Flooring but not Machined" or vice versa.

Any tips on how to do this?

[#2]

It may be a kludge, but functionally, you can achieve this using 'select excluded'.

Using your example Paul:

"Show me customers who have bought Flooring but not Machined"

Select all of the people who have bought machined.

Negate the selection (using select excluded on a table that shows your customers (not products) thereby selecting all of the people who have NOT purchased machined. Then, select 'flooring', and you should have your user selection.

There's likely an elegant solution using set analysis, or buttons, and macros and whatnot, but that's the basics of the approach.

[pkelly]

Thanks Ryan...

Select Excluded is something which we played around with this morning and, as you have sugggeste, it may do the job.

We are looking for a more elegant solution - one where the user can select from list box A (bought this) then list box B (but didn't buy this).

Thank you for the feedback - it may be the route we have to explore further...

Regards

Paul

[#4]

Hi Paul,

Totally agreed.

I've been thinking about this some more, it's a really good requirement, and something I'd like to implement in a better way. While this solution might work well for the power user, training lesser mortals on advanced topics like selecting excluded is a major pain.

I have an idea.

You could extract the distinct values you want to 'invert' from the table, and create a separate table that's disconnected from your model containing just that one field.

Expose that field on the workspace, and then in any element that you want to respect your inversion selection, you could add a set analysis statement that handles the negation of the values held in this separate field. I've never tried to use set analysis to do a negation in this way, so I'm not 100% if it will work, but it's an approach you could take a stab at.

You'd have to use some nested sets, and the possible operator… I think it could work?

[#5]

Try something like this:

Click on the listbox for your Customers field and start typing this expression =sum(if(product_family='Sawn',1)>0 and sum(if(product_family='Machined',1)<1

That should select customer who have invoices on file for Sawn products but no invoices for Machined. You can also use an expression like this in the Advanced Search dialog (right-click on a listbox and select Advanced Search).

If you're going to do that sort of thing all the time consider having a button that does the selection, or, make a calculated dimension that does something similar. Consider making a cycle group that shows the various choices for dimension so the report user could choose between "All Customers", "Sawn Product Customers", "Machined Product Customers", etc.

[#6 · Accepted solution]

Missing a few parentheses:

=sum(if(product_family='Sawn',1))>0 and sum(if(product_family='Machined',1))<1

note that it is an expression you start typing in a listbox

[pkelly]

Thanks Tim

Been playing around with this approach a bit this morning and think this will do the trick.

Thanks...

Paul

[sunil2288]

Hey Paul,

You Can try it Using Pereto select in button object ..