topic Re: Looking for rows with latest date in QlikView

Looking for rows with latest date

Anonymous — Sun, 29 May 2016 16:33:42 GMT

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

Re: Looking for rows with latest date

oknotsen — Sun, 29 May 2016 19:11:03 GMT

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.

Re: Looking for rows with latest date

trdandamudi — Sun, 29 May 2016 20:29:44 GMT

May be one of the following should work:

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

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

Hope this helps....

Re: Looking for rows with latest date

sunny_talwar — Sun, 29 May 2016 21:10:35 GMT

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

Re: Looking for rows with latest date

Anonymous — Tue, 31 May 2016 08:06:11 GMT

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

Re: Looking for rows with latest date

Anonymous — Tue, 31 May 2016 08:08:10 GMT

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

Re: Looking for rows with latest date

Anonymous — Tue, 31 May 2016 08:09:02 GMT

Hi Thirumala,

Thanks, works a treat.

Cheers,

Dave

Re: Looking for rows with latest date

sunny_talwar — Tue, 31 May 2016 12:37:51 GMT

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