How do I group values in a bar chart ?

[#1]

Hi,

What is the best method for summing data records into bands ? In the script ? Or in set analysis ?

I have thousands of transactions of sales data made up of the following fields....

Sale ID

Date of Sale

Sale Amount (in £)

Salesperson

I want to create a simple bar chart showing the number of salespersons ('Y' axis) who have a total sales figure between £0 and £100,000; between £100,001 and £200,000; between £200,001 and £300,000....and so on, in groups or bands of £100,000. The bands will appear on the 'X' axis.

So, for example, Salesman A who has sales of £650,000 will count as 1 salesperson in the £600,001 - £700,000 column band of the chart.

How should I approach this?

Your advice is much appreciated

MV

[maxgro]

look at class function

from Qlik help

class(expression, interval [ , label [ , offset ]])

Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

Examples:

class( var,10 ) with var = 23 returns '20<=x<30'

class( var,5,'value' ) with var = 23 returns '20<= value <25'

class( var,10,'x',5 ) with var = 23 returns '15<=x<25'

in bar chart you can use this as calculated dimension

class([Sale Amount], 100000)

and this as expression

count(Salesperson)

[lironbaram]

hi

you do it like this

in the dimension you use calculate dimension

with this expression :

class(aggr(sum([Sale Amount (in £)]),Salesperson),'X',100000)

and to count the sales persons use this expression

count(distinct Salesperson)

[#4]

Thanks Massimo,

Is there any way to display the x-axis labels differently. The first label on my chart is being displayed like this:

1 <=x<100001

Can I edit it like this....

1 - 100k

or this....

£1 - £100k

[sunny_talwar]

You might have to play around with it a little, try something like this as your calculated dimension:

=SubField(Class(), '<', 1) & '-' & Floor(SubField(Class(), '<', 3)/1000) & 'k' // Use the class function you have right now within the SubField function

For example, this in a text box

=SubField('1 <=x<100001', '<', 1) & '-' & Floor(SubField('1 <=x<100001', '<', 3)/1000) & 'k'

gives this:

HTH

Best,

Sunny

[MarcoWedel]

Or

Aggr(Class(Sum(...

Regards

Marco

[Anonymous]

Dear Maureen,   

Use replace() function for replacing '<= x <' with '-'.

=Replace(Class([Sale Amount], 100000),'<= x <', '-')

Regards,

Neetha

[MarcoWedel · Accepted solution]

Hi,

maybe like this:

=Aggr(Dual(Money(SubField(Class(Sum([Sale Amount]),100000),' <= x < ',1),'£#,##0')&' - '&Money(SubField(Class(Sum([Sale Amount]),100000),' <= x < ',2),'£#,##0'),Class(Sum([Sale Amount]),100000)),Salesperson)

or this:

=Aggr(Dual(Money(SubField(Class(Sum([Sale Amount]),100000),' <= x < ',1)/1000,'£#,##0k')&' - '&Money(SubField(Class(Sum([Sale Amount]),100000),' <= x < ',2)/1000,'£#,##0k'),Class(Sum([Sale Amount]),100000)),Salesperson)

hope this helps

regards

Marco

[#9]

Thanks to everyone who helped. I tried out each of your solutions on my application.

I do like Marco's though ! Very well explained with an example application and additional screenshots.

That's a great help Marco

MV

[#10]

One more thing Marco. How do I show those groups/bands on the x axis which have a value of 0 ?