Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

How to find Best & Worst Customer By Sale ?

Hello Deras.

i want Find Best & Worst Customer By Sale.

100.png

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

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

SumSale:

Load customer,Sum(Saleamount) as SumSaleAmount

  Resident Sale

  Group By customer ;

101.png

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

Tags (3)
1 Solution

Accepted Solutions

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:

Capture.PNG

Attaching the qvw for reference.

HTH

Best,

Sunny

View solution in original post

8 Replies

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:

Capture.PNG

Attaching the qvw for reference.

HTH

Best,

Sunny

View solution in original post

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

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

Not applicable

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?

Please Help me.

Thanks

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

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;


Capture.PNG

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

Not applicable

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:

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;


Capture.PNG

Not applicable

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

very fantastic

thank you

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

No problem