topic Re: Display top 3 customers in QlikView

Display top 3 customers

Thu, 10 Oct 2013 05:30:24 GMT

hi,

I want to get top 3 customers based on total balance(deposit column - withdrawl column) I have used Rank function where em getting all ranks based on total balance. Where highest balance takes highest rank number.

Ex: 10 refers to highest total balance.

I have used below code in expression

Rank((Deposit_Amt - Withdrawl_Amt),4,0)

How can I refine it to get top 3 based on highest bal?

Regards,

Prajna

Re: Display top 3 customers

saumyashah90 — Thu, 10 Oct 2013 05:32:40 GMT

Rank(Expression)<=3

Re: Display top 3 customers

Thu, 10 Oct 2013 05:36:55 GMT

Hi,

I had tried that but it doesn't work.. can u tell me what expression refers to

I Tried

Rank ((Deposit_Amt - Withdrawl_Amt),4,0)<=3

as well as

Rank((Deposit_Amt - Withdrawl_Amt)) <=3

earlier..

Regards,

Prajna

Re: Display top 3 customers

saumyashah90 — Thu, 10 Oct 2013 05:38:19 GMT

If(AGGR(Rank((Deposit_Amt-Withdrawl_Amt)),Customer)<=3,Customer)

Re: Display top 3 customers

Thu, 10 Oct 2013 05:47:43 GMT

Hi Try This,

=aggr(if(rank(sum(Deposit_Amt-Withdrawl_Amt))<=3,Customer),Customer)

Re: Display top 3 customers

Thu, 10 Oct 2013 05:55:15 GMT

Hi,

This works fine.. but how can I get rank in display in my chart like 1,2,3 for top 3? where 1 is the highest?

Regards,

Prajna

Re: Display top 3 customers

Thu, 10 Oct 2013 06:04:05 GMT

Hi,

This gives wrong result

As suggested this works fine

Rank (AGGR(Rank((Deposit_Amt-Withdrawl_Amt)),Cust_Id)<=3,Cust_Id)

but it doesn't display rank value

Regards,

Prajna

Re: Display top 3 customers

saumyashah90 — Thu, 10 Oct 2013 06:49:30 GMT

Just sort it
Suppose your expression name is Top3Customers so go to sort and under Numeric show ascending or descending

Re: Display top 3 customers

Thu, 10 Oct 2013 07:02:43 GMT

The customer specified in the code

If(AGGR(Rank((Deposit_Amt-Withdrawl_Amt)),Customer)<=3,Customer)

cant it be customer name any constraint like return type should be int as we have agg ?And one more thing cant I get a display like Rank 1, Rank 2, Rank 3.

Regards,

Prajna

Re: Display top 3 customers

saumyashah90 — Thu, 10 Oct 2013 07:17:18 GMT

Try using 'Rank'&Autonumber(Customers) as Rank

Re: Display top 3 customers

saumyashah90 — Thu, 10 Oct 2013 07:19:59 GMT

Please mark it helpful if it really was

Re: Display top 3 customers

Thu, 10 Oct 2013 07:28:30 GMT

hi,

The rank expression u had mentioned gives overall rank .

If I add year as a dimension rank is not varying w.r.t year.. its constant for all the year chosen. What could be done?

Regards,

Prajna

Re: Display top 3 customers

saumyashah90 — Thu, 10 Oct 2013 07:31:26 GMT

You should not year in dimension.that doesnt make sense

make it as a filter and if customer data is associated with the date field than it should make the difference

Re: Display top 3 customers

Thu, 10 Oct 2013 07:39:09 GMT

hi,

Year,month is my selection criteria I have taken cyclic drill to get the same. Based on that i want my ranks to be displayed.

Thanks and Regards,

Prajna