topic Re: Last Contact per Customer in QlikView

Last Contact per Customer

jrdunson — Mon, 25 Jan 2016 18:05:22 GMT

Hey everyone

How do we find the latest date (max Contact_Date) , for each salesman,.per customer (and show other columns). For example, I want to take this data... (see Sheet1 in attached spreadsheet) [ I also will need min Contact_Date also)...

And come up with this

Simple put: group by Salesman_ID, Customer_ID... find max/min contact dates... and show the comments field [though I probably need the record_no too... that is, if there are two records, each with the same max dates, show only one row...so group by Salesman_ID, Customer_ID...+ max(rec_no) ...presuming the max rec_no is the last entered, etc.)

I've tried the Aggr() function and FirstSortedValue. I can find the max date, but haven't worked out how to show only the row with the max date... I'll keep trying, but any help would be appreciated...

Some attempts:

aggr( max(Contact_Date), Salesman_ID, Customer_ID)
If (Contact_Date = aggr( max(Contact_Date), Salesman_ID, Customer_ID), date(Contact_Date), null())
... or use max(aggr( max(Contact_Date), Salesman_ID, Customer_ID))
sum( if(Contact_Date = aggr( max(Contact_Date), Salesman_ID, Customer_ID), 1))
FirstSortedValue .. but for two dimensions?

Attached is a sample qvw, MS Word Description file, and Sample spreadsheet.

Thanks ahead of time.

Jarrell

Re: Last Contact per Customer

Gysbert_Wassenaar — Mon, 25 Jan 2016 18:24:17 GMT

Use only Salesman_ID and Customer_ID as dimensions and as expressions

RecordNo: FirstSortedValue(RecordNo, -Contact_Date)

Comments: FirstSortedValue(Comment, -Contact_Date)

Contact_Date: Max(Contact_Date)

Re: Last Contact per Customer

jrdunson — Mon, 25 Jan 2016 19:58:05 GMT

Gysbert, Thanks... that works... not sure, though, why the data model didn't work on some of those...like:

aggr( max(Contact_Date), Salesman_ID, Customer_ID)
If (Contact_Date = aggr( max(Contact_Date), Salesman_ID, Customer_ID), date(Contact_Date), null())

But thanks, your method worked...

Jarrell

Re: Last Contact per Customer

sunny_talwar — Mon, 25 Jan 2016 20:00:13 GMT

If Gysbert's responses helped you get the correct answer, I would suggest marking his response as the correct answer (Qlik Community Tip: Marking Replies as Correct or Helpful)

Best,

Sunny

Re: Last Contact per Customer

jrdunson — Mon, 25 Jan 2016 20:34:58 GMT

done

Re: Last Contact per Customer

sunny_talwar — Mon, 25 Jan 2016 20:37:42 GMT

Thank you