Show the Sales person for most recent, last, latest sales order in Straight table

[SBN]

Hi there fellow Qlikkies,

I've been trying to get my head around the question on how to show the "Sales person" for the latest sales order from a client in a Straight Table.

This is a simplified representation of my data:

Order	Date	Customer	Sales person
1	1-5-2023	Customer1	Sales person 1
2	1-5-2023	Customer2	Sales person 2
3	2-5-2023	Customer1	Sales person 1
4	2-5-2023	Customer2	Sales person 2
5	3-5-2023	Customer1	Sales person 1
6	4-5-2023	Customer2	Sales person 2

I'd like to achieve the following:

Last order	Last order date	Customer	Sales person
5	3-5-2023	Customer1	Sales person 1
6	4-5-2023	Customer2	Sales person 2

Last order = Max([last order])
Last order date = Max([order date])
Customer = [Customer]
Sales person = ???

I've tried all sorts of variations with "FirstSortedValue", for the first time, but didn't succeed.
Maybe this is the wrong function to use, I don't know...😉

Anyone an idea?

Thanks in advance,

SBN

[deepanshuSh]

Hi There, 

If you are trying to do it on the backend its quite simple, 

Load 

Customer

max(date) as max_date

Resident Table1

Group by Customer. 

 Now, just do a right join to the above table and you will get the desired result. 

For the front end you can achieve the same using the set analysis and aggregation function. 

aggr(Max(date), Customer, Order, salesperson)

[brunobertels]

hi 

firstsortedvalue should work 

we need the max order date 

so it is max(Date) 

but by customer 

so it is aggr(max(date),customer) 

then now we want to extract the sale person corresponding 

firstsortedvalue( [sales person], - aggr(max(Date),Customer)) 

use the same logic to built your straight table : 

Dim Order 

=aggr(firstsortedvalue( Order, aggr(max(Order),Customer)) ,Order)

Dim Date :

=aggr(max(Date),Customer)

Customer : 

Customer

Sales person : 

firstsortedvalue( [Sales person], -aggr(max(Order),Customer)) 

[SBN]

@deepanshuSh and @brunobertels,

Thank you both for taking your time to answer my question. Really appreciate it!!

I tried this in my customer app, but can't get it to work.

Also in my simplified representation I can't get it to work.

I've attached a sample app with the simplified representation of the data model.

Would you be so kind to look at what might go wrong please?

Thanks again,

SBN

[Kushal_Chawda · Accepted solution]

@SBN  see the attached

[SBN]

Hi @Kushal_Chawda,

Thank you for your time and efforts to adjust my test app!

This works like a charm!👍
Also in my customer app.
So thank you very much.

@deepanshuSh and @brunobertels thanks again for your suggestions and time. Maybe I can do something for you next time.

Kind regards,

SBN