Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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....
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.
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....
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
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
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
Hi Thirumala,
Thanks, works a treat.
Cheers,
Dave
Not sure how Max() works sometime, but it is always good to add it to make sure it doesn't betray you another time.