Bottom 5 Customer According to their Sales

[victor5995]

Hello,

I have requirement to show bottom 5 customer as per their sales in Straight table. So I have Customer ID as Dimension and Sum(Sales) as Expression. I am using Dimension Limit in straight table to show  bottom 5 customer by selecting smallest 5.

But the issue I have is the data are as below. I have same number repeated for Sales value for different customer so bottom 5 is not displaying anything. What I want is to display Customer ID's 6 to 10 with their sales of $5,000,000.

CustomerID             Sales

1                                  $10,000,000

2                                  $9,000,000   

3                                  $8,000,000

4                                  $7,000,000

5                                  $6,000.000

6                                  $5,000,000

7                                  $5,000,000

8                                  $5,000,000

9                                  $5,000,000

10                                $5,000,000

I tried to put below as dimension as well but still its not displaying.

aggr(if(rank(-sum(Sales))<=10,CustomerID),CustomerID)

can someone help on this ?

[sinanozdemir]

Something like this:

Expression in the Bottom 5 field is:

I am attaching the qvw.

Hope this helps.

[effinty2112]

Hi Victor,

                    You could write a expression using the rank function but a simpler solution might be to set Dimension Limit as below:

Giving a table like:

CustomerID	Sum(Sales)
6	5000000
7	5000000
8	5000000
9	5000000
10	5000000

[victor5995]

Hi Sinan,

I want the representation as below in Straight table only. According to you i can get only Bottom 5 Customer Id not their sales.

CustomerID	Sum(Sales)
6	5000000
7	5000000
8	5000000
9	5000000
10	5000000

[sinanozdemir]

You can simply change the expr to the below:

[victor5995]

Hi Andrew,

As I mentioned in my post I tried this solution but this is not working for me when I am trying this with real data coming from database.

[victor5995]

This solution is also does not working with my data.

[sinanozdemir]

Hi,

Well, you may need to post another data-set that represents your problem better or closer to your actual problem. Based on the sample data you provided both solutions should work.

Thanks

[victor5995 · Accepted solution]

Hi Andrew,

Same solution worked for me. The issue was due to multiple records with zero values.

Thanks