previous year unit costs

[Anonymous]

Hello,

I was hoping someone could help me as I am new and stuck on something.  We have a transaction table that is based on PO# and product codes.  there are also unit_cost and delivery_date fields.

So right now we can choose a year and month to filter our data (delivery_date would fall into these selections).  What I am being asked to do is to show the previous year, same month unit cost...and if there were no products purchased in last year same month, then use the previous month to that.

So example, I choose 2016 and June.  My data shows this:

PO#          Product Code          Unit Cost          Delivery Date

10               Product A               10                    6/25/2016

If this same product had a delivery date of June 2015 then it would look like:

PO#          Product Code          Unit Cost          Delivery Date          Last Year Date          Last Year cost

10               Product A               10                    6/25/2016               6/12/2015                    12

if there was no delivery of this product in June of 2015, then go back to the last month that did have a delivery.

Does this make sense?  Is this even possible?

Thanks

Brandon

[johnw]

I think it makes sense to me. It should be possible. Would you be able to post some sample data (doesn't have to be real, just representative), either an inline load or a spreadsheet or something that would let me play around with the data model? Because my first thought is to build a structure in the data model, with tables something like this:

Transactions:
PO#, Product Code, Unit Cost, Delivery Date

Linkage:
PO#, Comparable PO#

Comparables:
Comparable PO#, Comparable Delivery Date, Comparable Unit Cost

You can eliminate the linkage if a Transaction always has a single Comparable PO#, but when you say "last year same month" I can imagine that there might be multiple PO#s to average, or take the max unit price, or something. Can there be more than one PO# for the product in a month? If so, which one do we use for the "Last Year Date" and "Last Year Cost"?

Saying "build these tables" is only half the solution, though, since the next question would be "how?" And... I'm not sure yet. I think I need to fiddle around.

There may also be some clever expression that could find the comparable(s) on the fly. Like maybe use set analysis to ignore the date selections, then look for the max([Delivery Date]) that's at least earlier than the end of the month a year ago, and grab the data for that. Again, I'd need sample data to fiddle around with because the right expression isn't jumping out at me.