Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
SBN
Contributor III
Contributor III

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

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

Labels (3)
1 Solution

Accepted Solutions
Kushal_Chawda

@SBN  see the attached

 

View solution in original post

5 Replies
deepanshuSh
Creator III
Creator III

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)

Trial and error is the key to get unexpected results.
brunobertels
Master
Master

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

 

brunobertels_1-1684339560077.png

 

SBN
Contributor III
Contributor III
Author

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

SBN_0-1684352672705.png

SBN_1-1684352745321.png

 

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

@SBN  see the attached

 

SBN
Contributor III
Contributor III
Author

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