Looking for rows with latest date

[Anonymous]

Hi,

I suspect that there is a very simple answer to this but I can't find it!

I have the following data;

DateFld	ProductID	BG
2014-10-28	1	A
2014-10-29	2	A
2014-10-30	3	B
2014-10-31	4	B
2014-11-01	5	C
2014-11-02	6	D
2014-11-02	7	E

I need to produce a SUM for all rows with the latest date.I have tried the following, but I get an answer of 28:

='Match to last date: ' & Sum( {1<[DateFld] = {"=max(DateFld)"}>}  ProductID)

As I said, this gives an answer of 28 which is the SUM(productid) from all rows, not just those for 2014-11-02. So it looks like my MAX function is not going across the entire set - although I've used the '1' in the set analysis. I'm obviously misunderstanding something here.

All help much appreciated.

Cheers,

Dave

[oknotsen]

Hi,

Please do not (try to) start the same topic multiple times. I have rejected and deleted your other copy and approved (only) this one.

As for your question:

I am not completely sure what you are trying to achieve. The sum of an ID field feels weird. You probably want to change that SUM by a Count(distinct ProductID) with some Set Analysis in there.

Can't check for you as I am not behind a PC with QV installed.

[trdandamudi · Accepted solution]

May be one of the following should work:

=Sum({1<DateFld={'$(=Max(DateFld))'}>} ProductID)

Or

=Sum({1<DateFld={'$(=Max(Date(DateFld,'DD/MM/YYYY')))'}>} ProductID)

Hope this helps....

[sunny_talwar]

Try this:

='Match to last date: ' & Sum({1<DateFld = {"$(=Date(Max(DateFld)))"}>}  ProductID)

1. You missed the dollar sign expansion

2. You missed the Date function Dates in Set Analysis

[Anonymous]

Hi Onno,

my apologies for breaking the rules.

I agree that 'sum(productid)' doesn't make sense on the face of it, my example has 'morphed' through a couple of changes until I could get down to a very simple example - and I forgot to change the column name!

Think of the column as SalesValue instead of ProductID.

Cheers,

Dave

[Anonymous]

Hi Sunny,

Thanks (again) for your help. As you and others said I missed out the 'S' expansion and adding it allows me to do what I need.

I found that I didn't need the Date function, it seemed to work fine without that.

Cheers,

Dave

[Anonymous]

Hi Thirumala,

Thanks, works a treat.

Cheers,

Dave

[sunny_talwar]

Not sure how Max() works sometime, but it is always good to add it to make sure it doesn't betray you another time.