Sales and Returns pivot table

[#1]

First post...hello all!

I'm working with two tables, sales and returns.

Sales table has sale date, sale tran #, and sold qty.

Return table has return date, sale tran #, sale date and return qty.

i need to create a pivot table that will show the sales (current month for example), and display if there was a return, the return date and qty.

Right now, i see every instance of a return, not just the one associated to that sale.

	sale date	trans #	return date	sale tran #	sale qty	return qty
total					1	1
item a	6/1/2010	1900	4/1/2008	1625	1	1
			5/1/2009	1752		1
			6/3/2010	1900		1

Can set analysis be used to solve this problem?

Thank you,

[pover]

What's your data model like? For this particular report you should link the sales and returns table by using the "sales tran #'.

Then you don't have to worry about doing set analysis or a sum(if).

Regards.

[#3]

i don't control the data model, what if my IT counterpart is not able to link by sales tran #?

[johnw · Accepted solution]

scarvajal wrote:
i don't control the data model, what if my IT counterpart is not able to link by sales tran #?<div></div>

Well, the right thing to do is probably to link the fields in the data model. Either "trans #" should be renamed to "sale tran #" or vice versa during the load script.

If that isn't practical for some reason, you can link the two fields up in the expression(s). For instance:

sum(if("trans #"="sale tran #","sale qty"))

The problem is that performance will be horrible if your data model is large. It'll be fine if its a fairly small set of data, though, so it might be good enough.

Edit: On the other hand, if these fields aren't linked in the data model, you'll probably have an endless number of headaches, of which this is merely the first. Coming up with an ugly workaround for every single problem is really not the way to handle it. It's like saying you want to drive across the country, but want to do it without tires on the car. Technically doable, but ill-advised. You may not be the mechanic, but someone needs to put tires on the car.

[#5]

great advice John, thank so much