topic Re: last order for day in QlikView

last order for day

Thu, 26 Sep 2013 19:29:31 GMT

Hi, I would need to get the last total of price by a date and customer, for example

customer	orderid	value	date
x	1	3000	2013/09/26 12:00
x	2	1500	2013/09/26 16:00
y	3	4500	2013/09/27 17:00
y	4	5000	2013/09/29 18:00

so I would need of last value for customer and date, what I would need:

Customer	Orderid	value	date
x	2	1500	2013/09/26 16:00
y	4	5000	2013/09/29 18:00

I tried to use the aggr function with max AGGR(max(date),customer) ,for to catch the last date and , I tried to use set analisys, sum({$<date={AGGR(max(date)}>} Value ) , to catch the value relative to the last date.

someone , can help me?

thanks

Re: last order for day

CELAMBARASAN — Fri, 27 Sep 2013 00:56:57 GMT

You can do it in two ways one in script and another in chart itself.

Chart:

Dimension: Customer

Expressions:

orderid = FirstSortedValue(orderid, -Num(Date))

value = FirstSortedValue(value, -Num(Date))

Date = TimeStamp(Max(Date))

Script:

Create a flag to identify the last order using any one of the following methods.

Orders:

Load

customer,

orderid,

value,

date

From source;

Left Join(Orders)

Load

Customer,

FirstSortedValue(orderid, -Num(date)) AS orderid,

1 AS LastOrder

Resident Orders;

Then use expression as Sum({<LastOrder={1}>} value)

Another way is something like

Orders:

Load

customer,

orderid,

value,

date,

if(Previous(customer) <> customer and RowNo()>1, 1) AS LastOrder

From source

Order By customer, date;

Then use expression as Sum({<LastOrder={1}>} value)

Re: last order for day

Fri, 27 Sep 2013 14:53:30 GMT

Thank you very very much...

I forgot to said that I needed of aggregation forms, but with your help, I get !

was quite this form

FirstSortedValue(AGGR(SUM([value]),[customer]),-NUM(AGGR(MAX(date),[customer])))

AGGR(MAX(date),[customer])

thank you again !

regards...