Last Price

[#1]

Hi

I have asked this question before and was asked to provide an example. It dropped off the radar for a bit but is now back.

I am bringing in all the purchase receipts, what I want to be able to calculate is the last price paid to a supplier for a product at a site.

On the attached example I have created what I think should work using a calculated dimension. but this doesn't seem to work.

Any help much appreciated

Matt

[#2]

You have to aggregate the calculate dimension to all dimensions which are used previous:

=aggr(max("Date([Requested Receipt Date])"),site,supplierno,ItemNo)

Regards.

[#3]

Thanks! This is what I've been looking for. I have one addtiional complication, I have dates with blank unit prices. Is there anyway to aggregate this data only for dates with a valid unit price?

[johnw]

Assuming anything non-null is a valid price, I think this:

=aggr(max(if(len("Price"),"Date([Requested Receipt Date])")),site,supplierno,ItemNo)

[#5]

No luck so far. I've attached a file. Hopefully this helps. For some reason, this produces the same results.