topic Re: Creating Bucket wise data? in QlikView

Creating Bucket wise data?

dmohanty — Mon, 28 Apr 2014 18:25:35 GMT

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?

Re: Creating Bucket wise data?

its_anandrjs — Mon, 28 Apr 2014 18:33:07 GMT

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;

Re: Creating Bucket wise data?

MK_QSL — Mon, 28 Apr 2014 19:01:51 GMT

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

Re: Creating Bucket wise data?

dmohanty — Mon, 28 Apr 2014 20:46:35 GMT

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

Re: Creating Bucket wise data?

MK_QSL — Mon, 28 Apr 2014 20:51:01 GMT

Glad that my answer helps you !

Happy Learning and Qliking !

Re: Creating Bucket wise data?

KC3 — Tue, 26 Dec 2023 18:34:39 GMT

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