8 Replies Latest reply: Jul 24, 2015 2:28 PM by Sunny Talwar

# How to find Best & Worst Customer By Sale ?

Hello Deras.

i want Find Best & Worst Customer By Sale.

i want Group by Per Customer And Calculate Sum (Total)

-------------------------------------------------------------------------------------------------------------------------

SumSale:

Resident Sale

Group By customer ;

But I can not find my first record and last record with customer. (Find Best And Worst)

i want Show This Sample  by Text Object:

D=>Best   =>720

A=>Worst =>220

• ###### Re: How to find Best & Worst Customer By Sale ?

Two expressions:

=FirstSortedValue(customer, -Aggr(Only(SumSaleAmount), customer)) & ' => Best =>' & Max(SumSaleAmount)

=FirstSortedValue(customer, Aggr(Only(SumSaleAmount), customer)) & ' => Best =>' & Min(SumSaleAmount)

Output:

Attaching the qvw for reference.

HTH

Best,

Sunny

• ###### Re: How to find Best & Worst Customer By Sale ?

Another option is to not create the sum in the script:

Sale:

month,

quantity,

amount

FROM

Community_173963.xlsx

(ooxml, embedded labels, table is sales);

Expressions:

=FirstSortedValue(customer, -Aggr(Sum(amount), customer)) & ' => Best =>' & Max(Aggr(Sum(amount), customer))

=FirstSortedValue(customer, Aggr(Sum(amount), customer)) & ' => Best =>' & Min(Aggr(Sum(amount), customer))

Output stays the same as above

• ###### Re: How to find Best & Worst Customer By Sale ?

Thank a lot sunny

This Perfect.

But I want Define Variable In Load Script And Calculate In Load Script.

Can he wrote the same code.

how?

Thanks

• ###### Re: How to find Best & Worst Customer By Sale ?

Script:

Sale:

month,

quantity,

amount

FROM

Community_173963.xlsx

(ooxml, embedded labels, table is sales);

Join(Sale)

Sum(amount) as SumSaleAmount

Resident Sale

Group By customer;

Temp:

'Best' as Flag

Resident Sale;

Concatenate (Temp)

'Worst' as Flag

Resident Sale;

Join(Sale)

Resident Temp;

DROP Table Temp;

Are you looking for this? Or are you looking for the exact definition of the variable getting created in the script???

• ###### Re: How to find Best & Worst Customer By Sale ?

very good.

In Spanish language : muy Perfecto.

But I want to into a variable

Let Variable

Let X=...

• ###### Re: How to find Best & Worst Customer By Sale ?

Here you go:

Sale:

month,

quantity,

amount

FROM

Community_173963.xlsx

(ooxml, embedded labels, table is sales);

Join(Sale)

Sum(amount) as SumSaleAmount

Resident Sale

Group By customer;

Temp1:

'Best' as Flag

Resident Sale;

Left Join(Temp1)

customer as MaxCus

Resident Sale;

LET vBest = Peek('MaxCus') & '=> Best =>' & Peek('MaxSumSaleAmount');

Temp2:

'Worst' as Flag

Resident Sale;

Left Join(Temp2)

customer as MinCus

Resident Sale;

LET vWorst = Peek('MinCus') & '=> Best =>' & Peek('MinSumSaleAmount');

DROP Tables Temp1, Temp2;

• ###### Re: How to find Best & Worst Customer By Sale ?

very fantastic

thank you

• ###### Re: How to find Best & Worst Customer By Sale ?

No problem