Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

12 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

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

Hope it helps you.

Regards,

Jagan.

Not applicable
Author

Hi Jagain

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

kind regards

Nayan

veidlburkhard
Creator III
Creator III

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
Creator III
Creator III

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

Burkhard

jagan
Luminary Alumni
Luminary Alumni

Hi,

PFA.

Regards,

Jagan.

Not applicable
Author

Thanks Burkhard.  Will try it

Not applicable
Author

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
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.