Regarding Top 1st Customer

[#1]

Hai,

Is there any way to display the Top 1^st customer and his sales value in Text box? Could anyone help me on this?

I'm using the following expression.

=if(Aggr(rank(sum(SALES)), CUSTOMER) =1 , CUSTOMER)

Thanks in Advance..

[#2]

This is what been written to retrieve to load the top 10 customer. I am sure with little modification you will get the required result. Let me know the out come after applying this logic.

=IF(
aggr(
rank(total
aggr( sum( {$<Year={$(=Max(Year)-1)}>} SalValue),itemname)
,0,1)
,itemname)
<=10,

aggr(
rank(total
aggr( sum( {$<Year={$(=Max(Year)-1)}>} SalValue),itemname)
,0,1)
,
itemname))

Thanks and Regards,

Rikab

[#3]

Hi,

try that one ;

only({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} CUSTOMER ) &' '& sum({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} Sales )

Best Regards,

[#4]

Wow..Its working fine..Thank you so much..

[#5]

Hello Onder!

Awesome Nice and useful one. I will be much thankful to you if you can explain how it works in such away that even beginners like me also can understand. Waiting for your reply!

Thanks and Regards,

Rikab

[johnw]

only({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} CUSTOMER ) & ' '
& sum({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} Sales )

The double quotes indicate that this is a search expression instead of a literal. QlikView will search for the Customer that is ranked #1 by sum(Sales).

only({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} CUSTOMER ) & ' '
& sum({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} Sales )

This is set analysis. It tells QlikView that we ONLY want to see the customer given inside of the brackets. What is inside of the brackets is the Customer that QlikView determined is ranked #1 by sum(Sales). So our set "selects" this specific customer.

only({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} CUSTOMER ) & ' '
& sum({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} Sales )

We have "selected" only one customer using set analysis, so only(... CUSTOMER) will return that customer.

only({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} CUSTOMER ) & ' '
& sum({<CUSTOMER= {"=rank(sum(Sales), CUSTOMER) =1"}>} Sales )

We have "selected" only one customer using set analysis, so sum(... Sales) will return the total sales for that customer.

[Oleg_Troyansky]

Folks,

let me offer an alternative, without Set Analysis, but with Advanced Aggregation:

=firstsortedvalue(Customer, -1* aggr(sum(Sales), Customer)) & ' ' &
firstsortedvalue( aggr(sum(Sales), Customer), -1 * aggr(sum(Sales), Customer))

Brief explanation:

• Function "FirstSortedValue" accepts 2 mandatory parameters (and some optional ones) and returns the Top value of the first parameter, sorted by the values of the second parameter. Multiplying by -1 is used to sort Sales in descending order.
• Function Aggr() aggregates sum(Sales) to the Customer level
• We have to use the same aggr() twice for the second "FirstSortedValue", because both parameters are mandatory.

Not sure which one of the two formulas performs better (I suspect that Set Analysis might win the competition) but I was just curious if the could be done with FirstSortedValue. It can, indeed!

cheers,

[#8]

Helo John!

Thanks for very very neat explanation! Will be useful for everyone to understand.

Thanks and Regards,

Rikab