Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jrdunson
Creator
Creator

Last Contact per Customer

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)...

raw_data.JPG

And come up with this

lastest_contact_sample.JPG

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
jrdunson
Creator
Creator
Author

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

sunny_talwar

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

jrdunson
Creator
Creator
Author

done

sunny_talwar

Thank you