Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

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

Community Browser