Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have requirement like this below:
I have Regions and each Region has some SalesPersonID. Each SalesPersonID has some Sales Quantity. Like this table below:
Region | SalesPersonID | Sales Quantity |
A | 101 | 2000 |
102 | 800 | |
103 | 70 | |
104 | 220 | |
B | 201 | 400 |
202 | 100 | |
203 | 750 | |
204 | 1800 | |
C | 301 | 1500 |
302 | 270 | |
303 | 60 | |
304 | 650 |
Now I have to create a interval/ Bucket value: 0-100, 100-250, 250-500, 500-1000, 1000+ . The count of Sales Person ID falls in which category of bucket. I have to show this in a Graph.
Could someone please show me with an example?
Let me try also
First of all, want to give you little correction.....
The Bucket should be 0-100, 101-250, 251-500, 501-1000, 1001+
====================
Now Create a Bar Chart
Dimension : Use Calculated Dimension instead of normal Dimension
=DUAL(
IF([Sales Quantity] > 0 and [Sales Quantity] <=100, '0-100',
IF([Sales Quantity] > 100 and [Sales Quantity] <=250, '101-250',
IF([Sales Quantity] > 250 and [Sales Quantity] <=500, '251-500',
IF([Sales Quantity] > 500 and [Sales Quantity] <=1000, '501-1000',
IF([Sales Quantity] > 1000, '1001+'))))),
IF([Sales Quantity] > 0 and [Sales Quantity] <=100, 1,
IF([Sales Quantity] > 100 and [Sales Quantity] <=250, 2,
IF([Sales Quantity] > 250 and [Sales Quantity] <=500, 3,
IF([Sales Quantity] > 500 and [Sales Quantity] <=1000, 4,
IF([Sales Quantity] > 1000, 5)))))
)
Expression
COUNT(SalesPersonID) or COUNT(Distinct SalesPersonID)
Go to Sort Tab and Select Numeric Value Ascending Order..
Also Make sure, you deselect everything in Sort Tab except Numeric Value
=========================================
QVW enclosed for your reference
=========================================
Hope this helps...
You can create a bucket in the load script like
Load
Region,
SalesPersonID,
If( ([Sales Quantity] >=0 and [Sales Quantity] ) <= 100,'0-100',
If( ([Sales Quantity] >=100 and [Sales Quantity] ) <= 250,'100-250',
If( ([Sales Quantity] >=250 and [Sales Quantity] ) <= 500,'250-500',
If( ([Sales Quantity] >=500 and [Sales Quantity] ) <= 1000,'500-1000',
If( ([Sales Quantity] >=1000 ,'1000+'))))) as Bucket
[Sales Quantity]
From Location;
Let me try also
First of all, want to give you little correction.....
The Bucket should be 0-100, 101-250, 251-500, 501-1000, 1001+
====================
Now Create a Bar Chart
Dimension : Use Calculated Dimension instead of normal Dimension
=DUAL(
IF([Sales Quantity] > 0 and [Sales Quantity] <=100, '0-100',
IF([Sales Quantity] > 100 and [Sales Quantity] <=250, '101-250',
IF([Sales Quantity] > 250 and [Sales Quantity] <=500, '251-500',
IF([Sales Quantity] > 500 and [Sales Quantity] <=1000, '501-1000',
IF([Sales Quantity] > 1000, '1001+'))))),
IF([Sales Quantity] > 0 and [Sales Quantity] <=100, 1,
IF([Sales Quantity] > 100 and [Sales Quantity] <=250, 2,
IF([Sales Quantity] > 250 and [Sales Quantity] <=500, 3,
IF([Sales Quantity] > 500 and [Sales Quantity] <=1000, 4,
IF([Sales Quantity] > 1000, 5)))))
)
Expression
COUNT(SalesPersonID) or COUNT(Distinct SalesPersonID)
Go to Sort Tab and Select Numeric Value Ascending Order..
Also Make sure, you deselect everything in Sort Tab except Numeric Value
=========================================
QVW enclosed for your reference
=========================================
Hope this helps...
Great Mr. Manish. You are really helpful
Exactly this is the way I wanted. Though I was clear on my requirement, but I got deviated a bit while posting it. You corrected me quite well
The way you have demonstrated, is really easy to understand and a good learning experience too.
Kudos and Thanks a lot again!!!
Glad that my answer helps you !
Happy Learning and Qliking !
Hi
In the same manner I have question if my sale person id has been in multiple lines how i will get the quantity calculated. After the total of then I need distinct count of Sale person ID.
Also I attached my excel for problem solving.
Region | SalesPersonID | Sales Quantity |
A | 101 | 1000 |
101 | 1000 | |
102 | 400 | |
102 | 400 | |
103 | 30 | |
103 | 40 | |
104 | 100 | |
104 | 120 | |
B | 201 | 100 |
201 | 300 | |
202 | 50 | |
202 | 50 | |
203 | 250 | |
203 | 500 | |
204 | 600 | |
204 | 1200 | |
C | 301 | 200 |
301 | 1300 | |
302 | 270 | |
303 | 60 | |
304 | 650 |
So pls help me with the solution