Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Attached is a sample qvw, MS Word Description file, and Sample spreadsheet.
Thanks ahead of time.
Jarrell
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)
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)
Gysbert, Thanks... that works... not sure, though, why the data model didn't work on some of those...like:
But thanks, your method worked...
Jarrell
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
done
Thank you