How do I display the correct price based on a max date?

building reports
1

HI all,
We have products with historic prices against them and each price has a date, I need to display the price based on the max price date. I’ve got the max price date showing correctly using the below

=Max([Price Date] In([P Code];[P Desc];[Price Date]))

but cant get [Gross Cost] to display the price I need, its either showing all of the historic costs or giving a multivalve error. Does anybody know what the correct syntax is required for this please?

I only want to see 2 rows:

image
image621×561 30.7 KB

2

This isnt working

image
image1033×185 11.6 KB

3

I’ve lost touch with WebI for a while but I’d do it with a “HAVING” clause in the SQL.

SELECT CustomerID, CustomerName, Price Date
FROM Customers
HAVING Gross Cost = MAX(Gross Cost)

Or this one, but you have to use an “n” that will include all ties for first place.

SELECT CustomerID, CustomerName, Price Date 
FROM Customers
ORDER BY Gross Cost DESC
FETCH FIRST n ROWS ONLY

Depending on your DB…
– DB2

SELECT * 
FROM table 
FETCH first N rows only

– Informix

SELECT first N * 
FROM table

– Microsoft SQL Server and Access

SELECT top N * 
FROM table

– MySQL and PostgreSQL

SELECT * 
FROM table limit N

– Oracle

SELECT * 
FROM (SELECT * FROM table ORDER BY Gross Cost DESC) 
WHERE rownum <= N
4

why are you switching from in() to foreach() ?
if there is a MULTIVALUE, try sum…, avg…, min… or max([Gross Cost])

5

something like =Max([Price Date] in{input context} in{output context}) might help