Report Criteria for a field that contains one value but not another

[DBergkamp10]

Hi,

Possibly a very silly question. I have report that I want to run to see which customers bought one product ID number, but who also did not by another specific product number.

The field for the product number is called Item_No.

How would I go about it?

Thx in advance!

 

[MCuthill]

DBergkamp10,

I would suggest a report with "Items Purchased" at the Detail-level, with a group on Customer (and a second by Purchase Date or Invoice ID, if you are looking for this criteria on a specific day or sale).

From here, I would create two formula fields - one for each scenario, such like:

{@Counter_ProductA}

IF {Table.ProductID}=##### THEN 1 ELSE 0

(where ##### is the product ID of Product A)

{@Counter_ProductB}

IF {Table.ProductID}=##### THEN 1 ELSE 0

(where ##### is the product ID of Product B)

Assuming you want persons who have bought Product A and not Product B, you would then create Group Selection such like:

Sum({@Counter_ProductA},{Table.GroupField})>0 AND
Sum({@Counter_ProductB},{Table.GroupField})=0

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[DBergkamp10]

Excellent, Thx Mike!