5 Replies Latest reply: Apr 1, 2016 5:58 PM by Sunny Talwar

# Average order amount of all first orders

Hi,

I have an table of all orders like that:

Customer
OrdernumberAmount
11

10,00

1215
2312,50
1413
3520
2623
3711
1810

and i need only the amount of the first orders.

Thanks for any helps.

• ###### Re: Average order amount of all first orders

Hi Markus,

Can you please try using the below script.

input:

Customer,    Ordernumber,    Amount

1,    1    , "10,00"

1,    2,    "15"

2,    3,    "12,50"

1,    4,    "13"

3,    5,    "20"

2,    6,    "23"

3,    7,    "11"

1,    8,    "10"

];

NoConcatenate

output:

Ordernumber,

Amount,

if(Customer<>Previous(Customer),1,Peek(rank)+1) as rank

Resident input Order by Customer, Ordernumber;

NoConcatenate

finaloutput:

Ordernumber as ordernumber,

Amount as amount

Resident output where rank=1;

Thanks,
Sreeman

• ###### Re: Average order amount of all first orders

You could try it with firstsortedvalue() on the order number or an order date.

- Marcus

• ###### Re: Average order amount of all first orders

Maybe this... ?

• ###### Re: Average order amount of all first orders

see this

• ###### Re: Average order amount of all first orders

Couple of options to remove them from script:

1) Using Right Join

Table:

Customer,    Ordernumber,    Amount

1,    1, 10.00

1,    2,    15

2,    3,    12.50

1,    4,    13

3,    5,    20

2,    6,    23

3,    7,    11

1,    8,    10

];

Right Join (Table)

Min(Ordernumber) as Ordernumber

Resident Table

Group By Customer;

2) Using FirstSortedValue (as Mentioned by Marcus Above)

Table:

Min(Ordernumber) as Ordernumber,

FirstSortedValue(Amount, Ordernumber) as Amount

Group By Customer;

Customer,    Ordernumber,    Amount

1,    1, 10.00

1,    2,    15

2,    3,    12.50

1,    4,    13

3,    5,    20

2,    6,    23

3,    7,    11

1,    8,    10

];

If you want to keep all the data but just want to show the Min ordernumber lines, then you can create flags for it

3) Flag

Table:

Customer,    Ordernumber,    Amount

1,    1, 10.00

1,    2,    15

2,    3,    12.50

1,    4,    13

3,    5,    20

2,    6,    23

3,    7,    11

1,    8,    10

];

Left Join (Table)