Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
Hi Andrew,
Same solution worked for me. The issue was due to multiple records with zero values.
Thanks
Something like this:
Expression in the Bottom 5 field is:
I am attaching the qvw.
Hope this helps.
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 |
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 |
You can simply change the expr to the below:
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.
This solution is also does not working with my data.
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
Hi Andrew,
Same solution worked for me. The issue was due to multiple records with zero values.
Thanks