Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to show the last date registered for purchasing products and the latest price (not highest) connected to this product.
I use a chart where the fields shown are defined by: article , max(DateofPurchase) and price. This will (as I filtered on latest date with the 'max') whos one record per article. However in this example the price wil be left blanc ( " - ").
How could I get the attached latest price connected to this purchase date ?
Source data:
article | date | price |
10.01 | 1-1-2012 | 12,5 |
10.01 | 5-1-2012 | 13 |
10.01 | 10-1-2012 | 10 |
11.50 | 1-1-2012 | 10 |
11.50 | 4-1-2012 | 11 |
11.50 | 12-1-2012 | 12 |
My chart should show the last purchase date (and this date's price).
Chart Result:
article | date | price |
10.01 | 10-1-2012 | 10 |
11.50 | 12-1-2012 | 12 |
If I use max(price) , i will receive this maximum price (e.g. 13 for article 10.01 ), but this is not what i want : i'm looking for the price at the last date. Maybe somethin like =last(price)
maybe using a filter like =( { < Date = {$(Max(Date))} > } would help, but somehow this syntax is not correct?
Update : Adding the max value (although I just expected one answer)
max ( {< Datum={$(=Max(Datum))} >} Laatste_Prijs) dit provide me with the correct answer...
Update 2: this solution will only work when filtering to one article, not wile showing a list of distinct articles.
Hi,
Try with this
For Date use Max(date),
For price use MaxString({<date={'$(=Max(date))'}>} price)
Celambarasan
I did find the same dicussion that also provided me a working answer:
http://community.qlik.com/message/154133
I think FirstsortedValue should work, you could use a negative date as second parameter to sort descending (latest date), like
= FirstSortedValue(Price, -Date)