Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

Please can you help.

kind regards

Nayan

Item CodeReceipt DateReceipt NumberQty ReceivedUnit Cost
ABC12320131206RCP000000071017200113.51
ABC12320131108RCP000000070385150117.27
ABC12320131101RCP000000070239150117.27
ABC12320131008RCP000000069536150117.27
ABC12320130624RCP000000067101300117.27
ABC12320130620RCP000000067050700113.04
ABC12320130405RCP000000065550300113.04
ABC12320130327RCP000000065354600113.04
ABC12320130305RCP000000064910500113.04
ABC12320121205RCP000000063375400107.11
ABC12320121030RCP000000062405450107.11
ABC12320121029RCP000000062365600107.11
ABC12320121012RCP000000062013134107.11
ABC12320120928RCP000000061779134107.11
ABC12320120914RCP000000061503134107.11
ABC12320120906RCP000000061279123107.11
ABC12320120903RCP00000006120410107.11
ABC12320120831RCP00000006118110107.11
ABC12320120831RCP00000006118310107.11
1 Solution

Accepted Solutions
MVP
MVP

Re: Last Receipted Cost

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.

12 Replies
MVP
MVP

Re: Last Receipted Cost

Hi,

Try like this

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

Hope it helps you.

Regards,

Jagan.

Not applicable

Re: Last Receipted Cost

Hi Jagain

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

kind regards

Nayan

veidlburkhard
Contributor III

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

veidlburkhard
Contributor III

Re: Last Receipted Cost

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

Burkhard

MVP
MVP

Re: Re: Last Receipted Cost

Hi,

PFA.

Regards,

Jagan.

Not applicable

Re: Last Receipted Cost

Thanks Burkhard.  Will try it

Not applicable

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

Not applicable

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

MVP
MVP

Re: Last Receipted Cost

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.

Community Browser