12 Replies Latest reply: Mar 17, 2014 7:53 AM by Nayan Lalla

# Last Receipted Cost

Hi

I have the follow table in my data base.  (see below)

In a chart table, I want to create a formula to show the last unit cost receipted, which is R113.51.

I've tried the following formulae , but does not work.

• if(max(Receipt Number),Unit cost)
• if(max(Receipt Date),Unit cost).

kind regards

Nayan

 Item Code Receipt Date Receipt Number Qty Received Unit Cost ABC123 20131206 RCP000000071017 200 113.51 ABC123 20131108 RCP000000070385 150 117.27 ABC123 20131101 RCP000000070239 150 117.27 ABC123 20131008 RCP000000069536 150 117.27 ABC123 20130624 RCP000000067101 300 117.27 ABC123 20130620 RCP000000067050 700 113.04 ABC123 20130405 RCP000000065550 300 113.04 ABC123 20130327 RCP000000065354 600 113.04 ABC123 20130305 RCP000000064910 500 113.04 ABC123 20121205 RCP000000063375 400 107.11 ABC123 20121030 RCP000000062405 450 107.11 ABC123 20121029 RCP000000062365 600 107.11 ABC123 20121012 RCP000000062013 134 107.11 ABC123 20120928 RCP000000061779 134 107.11 ABC123 20120914 RCP000000061503 134 107.11 ABC123 20120906 RCP000000061279 123 107.11 ABC123 20120903 RCP000000061204 10 107.11 ABC123 20120831 RCP000000061181 10 107.11 ABC123 20120831 RCP000000061183 10 107.11
• ###### Re: Last Receipted Cost

Hi,

Try like this

=Max({<[Receipt Date]= {'\$(=Date(Max([Receipt Date]), '"YYYYMMDD"))'} >} [Unit Cost])

Hope it helps you.

Regards,

Jagan.

• ###### Re: Last Receipted Cost

Hi Jagain

kind regards

Nayan

• ###### Re: Last Receipted Cost

Hi, Nayan,

you have to correct Jagans expression a little bit:

=Max({<[Receipt Date]= {'\$(=Date(Max([Receipt Date]), 'YYYYMMDD'))'} >} [Unit Cost])

this will work.

Regards

Burkhard

• ###### Re: Last Receipted Cost

Thanks Burkhard.  Will try it

• ###### Re: Last Receipted Cost

Nayan I mean the quotes have not been correct with Jagan's expression. The logic is ok.

Burkhard

• ###### Re: Re: Last Receipted Cost

Hi,

PFA.

Regards,

Jagan.

• ###### Re: Last Receipted Cost

Hi Jagan

Thank you for your reply. I see your your test model it works.  Let mke try it out and will get back to you.

kind regards

Nayan

• ###### Re: Last Receipted Cost

Hi Jagan

It worked .  Thank you.  Can the formula be done with last receipt number .

Reason why im asking is that there could be a possibility where an item is received twice on the same day at different costs.

kind regards

Nayan

• ###### Re: Last Receipted Cost

Hi,

Try this

Test:

[Item Code],

Date(Date#([Receipt Date], 'YYYYMMDD')) AS [Receipt Date],

[Receipt Number],

Num(Replace( [Receipt Number], 'RCP', '')) AS RecNum,

[Unit Cost]

FROM

(html, codepage is 1252, embedded labels, table is @1);

Expression:

=Max({<[RecNum]= {\$(=Max(RecNum))} >} [Unit Cost])

Regards,

Jagan.

• ###### Re: Last Receipted Cost

Hi Jagan

It worked. On your formula you are just missing the single inverted commas. After the opening curly bracket and before the closing curly bracket.

Thanks again for your help.  Much apprecaited.

kind regards

Nayan

• ###### Re: Last Receipted Cost

Hi,

No need of single inverted commas because it is a number.

Regards,

Jagan.

• ###### Re: Last Receipted Cost

Hi Jagan

Without the inverted commas, the formula looks like this

=

Max({<[Receipt Number]= {\$(=Max([Receipt Number]))} >}

UNITCOST)

kind regards

Nayan