5 Replies Latest reply: Jan 25, 2016 3:37 PM by Sunny Talwar RSS

    Last Contact per Customer

    Jarrell Dunson

      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