topic Re: Last Receipted Cost in QlikView

Last Receipted Cost

Mon, 17 Mar 2014 08:29:04 GMT

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).

Please can you help.

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

jagan — Mon, 17 Mar 2014 08:48:45 GMT

Hi,

Try like this

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

Hope it helps you.

Regards,

Jagan.

Re: Last Receipted Cost

Mon, 17 Mar 2014 08:57:28 GMT

Hi Jagain

Thanks for your reply, however , the formula did not work. Still gives a blank answer.

kind regards

Nayan

Re: Last Receipted Cost

veidlburkhard — Mon, 17 Mar 2014 09:20:59 GMT

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

veidlburkhard — Mon, 17 Mar 2014 09:23:08 GMT

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

Burkhard

Re: Re: Last Receipted Cost

jagan — Mon, 17 Mar 2014 09:25:23 GMT

Hi,

PFA.

Regards,

Jagan.

Re: Last Receipted Cost

Mon, 17 Mar 2014 09:33:17 GMT

Thanks Burkhard. Will try it

Re: Last Receipted Cost

Mon, 17 Mar 2014 09:36:36 GMT

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

Mon, 17 Mar 2014 09:55:36 GMT

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

jagan — Mon, 17 Mar 2014 10:07:46 GMT

Hi,

Try this

Test:

LOAD

[Item Code],

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

[Receipt Number],

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

[Qty Received],

[Unit Cost]

FROM

[http://community.qlik.com/thread/110831]

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

Expression:

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

Regards,

Jagan.

Re: Last Receipted Cost

Mon, 17 Mar 2014 10:23:11 GMT

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

jagan — Mon, 17 Mar 2014 10:26:14 GMT

Hi,

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

Regards,

Jagan.

Re: Last Receipted Cost

Mon, 17 Mar 2014 11:53:26 GMT

Hi Jagan

Without the inverted commas, the formula looks like this

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

UNITCOST)

kind regards

Nayan