topic Regarding Top 1st Customer in QlikView

Regarding Top 1st Customer

Wed, 24 Mar 2010 08:00:25 GMT

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

Regarding Top 1st Customer

Wed, 24 Mar 2010 08:21:10 GMT

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

Regarding Top 1st Customer

Wed, 24 Mar 2010 08:53:40 GMT

Hi,

try that one ;


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

Best Regards,

Regarding Top 1st Customer

Wed, 24 Mar 2010 09:09:50 GMT

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

Regarding Top 1st Customer

Wed, 24 Mar 2010 10:05:21 GMT

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

Regarding Top 1st Customer

johnw — Wed, 24 Mar 2010 23:11:48 GMT

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.

Regarding Top 1st Customer

Oleg_Troyansky — Thu, 25 Mar 2010 02:47:17 GMT

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,

Regarding Top 1st Customer

Thu, 25 Mar 2010 05:29:39 GMT

Helo John!

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

Thanks and Regards,

Rikab