Display clients that has not purchase productCode B ?

[sanchoniathon]

Hello to all,

WE need a report studio report that: Will DISPLAY all clients that has NOT made any purchase for the category product named "B"
- Columns to display are Region, client#, TotalSalesLast12Months and TotalSalesLast12Month(For category B)

Definition of the columns (metrics) are:
TotalSalesLast12Months: IT will display all product sales including those with category B
and
TotalSalesLast12Month(For product category B): It will display sales only for the product category B


Is this possible ?


Thanks !

[Michael75]

There's some contradiction here...

Since both your metrics include sales of category B, and one of them contains only that, what you really require is to:

DISPLAY all clients that HAVE made any purchase for the category product named "B"

Is that correct?

If so, I have an easy solution for this

[sanchoniathon]

Hi Michael75,

I think i need 2 queries joined together in order to perform a LEFT OUTER JOIN in order to show ALL clients wether or not they purchased product category B.

So when that paricular client#1 for example hasn't purchased Product category B, then the metric [TotalSalesLast12Month(For category B)] should display 0 $ but the other metric [TotalSalesLast12Months] will still display the appropriate sales (Product category B).

In this way we are able to look a that metric when it's at 0$.

If more precision is needed please let me know.

Thanks for your fast reply by the way !

[Lynn]

You have not mentioned if your source is relational or dimensional. Solutions are often going to differ dramatically depending on which type of source you have.

My suggestion that follows may be applicable ONLY IF your source is relational.

In one query you can have a query item for TotalSales and another for TotalSalesProductB. The first will just refer to the package item that shows sales. The second will be an expression something like this:

Code Select Expand


case
  when [TheProduct] = 'Category B' then [SalesAmount]
  else 0
end


You would then get all customers who bought anything in your filtered time period and could easily see which purchased Category B and which did not. If you only wanted those who DID NOT buy category B, you could use a detail filter set to 'after aggregation' and pick only those with TotalSalesProductB = 0.