calculate cost saving between two products

[Anonymous]

I have a list of products, one groups of products is generic the other is patented (so cost more) but do the same thing. What I'd like is to be able to calculate the cost a customer could have saved by moving the spending from the patented to generic product. If anyone could help it'd be very much appreciated. 

Product	Items	Cost
Patented	10	3.33
Patented	20	6.66
Generic	10	1.20
Generic	30	3.60

[Anonymous · Accepted solution]

HI @Anonymous ,

I'd like to suggest you add a group from the same products, then you can use this id column to find or correspond records. After these, you can simply compare records based on similar id groups and detail product types.

Regards,

Xiaoxin Sheng

[kentyler]

It seems that you might need a table that records which customer bought which product (and the date they bought it)
and a table of products, with the product price and the non-generic product it is a substitute for, and a flag for whether it's generic or not. (leaving aside whether one generic could sub for multiple non-generic)

Then you could add up customer purchases, and also add up what the total would be if they substituted generic for non-generic products in their "basket"

You could have a more complicated relationship between generic and non generic products with a bridge table that had fields for product, non-generic product that joined the table of products to itself and let there be many products that were linked to one non-generic product, and many non-generic products that were linked to a product.....then you would need to alter your comparison algorithm to look up the cheapest of potentially several alternatives to a product.
This is an interesting and potentially complex question. Let me know if you'd like to do a screen share and pursue it further. ken@8thfold.com