How to find Best & Worst Customer By Sale ?

[#1]

Hello Deras.

i want Find Best & Worst Customer By Sale.

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

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

SumSale:

Load customer,Sum(Saleamount) as SumSaleAmount

  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

[sunny_talwar · Accepted solution]

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

[sunny_talwar]

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

Sale:

LOAD customer,

    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

[#4]

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?

Please Help me.

Thanks

[sunny_talwar]

Script:

Sale:

LOAD customer,

    month,

    quantity,

    amount

FROM

Community_173963.xlsx

(ooxml, embedded labels, table is sales);

Join(Sale)

LOAD customer,

  Sum(amount) as SumSaleAmount

Resident Sale

Group By customer;

Temp:

LOAD Max(SumSaleAmount) as SumSaleAmount,

  'Best' as Flag

Resident Sale;

Concatenate (Temp)

LOAD Min(SumSaleAmount) as SumSaleAmount,

  'Worst' as Flag

Resident Sale;

Join(Sale)

LOAD *

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

[#6]

very good.

In Spanish language : muy Perfecto.

But I want to into a variable

Let Variable

Let X=...

[sunny_talwar]

Here you go:

Sale:

LOAD customer,

    month,

    quantity,

    amount

FROM

Community_173963.xlsx

(ooxml, embedded labels, table is sales);

Join(Sale)

LOAD customer,

  Sum(amount) as SumSaleAmount

Resident Sale

Group By customer;

Temp1:

LOAD Max(SumSaleAmount) as MaxSumSaleAmount,

  'Best' as Flag

Resident Sale;

Left Join(Temp1)

LOAD SumSaleAmount as MaxSumSaleAmount,

  customer as MaxCus

Resident Sale;

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

Temp2:

LOAD Min(SumSaleAmount) as MinSumSaleAmount,

  'Worst' as Flag

Resident Sale;

Left Join(Temp2)

LOAD SumSaleAmount as MinSumSaleAmount,

  customer as MinCus

Resident Sale;

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

DROP Tables Temp1, Temp2;

[#8]

very fantastic

thank you

[sunny_talwar]

No problem