Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |
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.
Hi,
Try like this
=Max({<[Receipt Date]= {'$(=Date(Max([Receipt Date]), '"YYYYMMDD"))'} >} [Unit Cost])
Hope it helps you.
Regards,
Jagan.
Hi Jagain
Thanks for your reply, however , the formula did not work. Still gives a blank answer.
kind regards
Nayan
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
Nayan I mean the quotes have not been correct with Jagan's expression. The logic is ok.
Burkhard
Hi,
PFA.
Regards,
Jagan.
Thanks Burkhard. Will try it
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
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
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.