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

How to find Best & Worst Customer By Sale ?

Fri, 24 Jul 2015 16:31:50 GMT

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

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

sunny_talwar — Fri, 24 Jul 2015 16:53:38 GMT

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 ?

sunny_talwar — Fri, 24 Jul 2015 17:02:51 GMT

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

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

Fri, 24 Jul 2015 17:25:18 GMT

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 ?

sunny_talwar — Fri, 24 Jul 2015 17:32:35 GMT

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

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

Fri, 24 Jul 2015 18:09:16 GMT

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 ?

sunny_talwar — Fri, 24 Jul 2015 18:19:09 GMT

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;

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

Fri, 24 Jul 2015 18:24:57 GMT

very fantastic

thank you

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

sunny_talwar — Fri, 24 Jul 2015 18:28:50 GMT

No problem