topic Re: How to get last order date in QlikView

How to get last order date

madhuqliklondon — Wed, 25 Nov 2020 16:16:04 GMT

Hi friends, I am trying to get last order date from customers.

customer no	order number	order date	name	country	email
1	21	2/2/16	Rich	UK	-
1	22	5/5/16	Rich	UK	-
2	41	7/5//16	Dave	USA	-
2	42	12/12/16	Dave	USA	-
3	51	01/12/16	Tom	GE	-

I am trying to get what is their last order so that re target the customer via email if they are going laps . Below table is ex . what I want to get . Don't want any repeat customers and want only last order.

customer no

order number

order date

name

country

5/5/16

Rich

7/5/16

Dave

USA

1/12/16

Tom

Please help to achieve it.

what dimensions and Expressions I have to use?

Column names: Customer_no, Order_number, Order_date,Name, Country and Email.

Re: How to get last order date

avinashelite — Wed, 07 Jun 2017 06:23:52 GMT

Try like this

Dimensions

1. Customer_no

2.Order_number

3.Name

4.Country

5.Email

Expression

Aggr(max(Order_date),Customer_no)

Re: How to get last order date

tresB — Wed, 07 Jun 2017 06:39:16 GMT

Use FirstSortedValue() like:

Straight table:

Dim: [customer no]

OrderNum (Exp): FirstSortedValue([order number],-[order date])

....

Re: How to get last order date

ahaahaaha — Wed, 07 Jun 2017 06:50:07 GMT

Hi,

as variant in attached file.