topic Re: How do I group values in a bar chart ? in QlikView

How do I group values in a bar chart ?

Sun, 19 Jul 2015 09:23:19 GMT

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

Re: How do I group values in a bar chart ?

maxgro — Sun, 19 Jul 2015 09:32:29 GMT

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)

Re: How do I group values in a bar chart ?

lironbaram — Sun, 19 Jul 2015 09:38:48 GMT

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)

Re: How do I group values in a bar chart ?

Sat, 25 Jul 2015 13:12:07 GMT

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

Re: How do I group values in a bar chart ?

sunny_talwar — Sat, 25 Jul 2015 13:31:41 GMT

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

Re: How do I group values in a bar chart ?

MarcoWedel — Sat, 25 Jul 2015 15:20:12 GMT

Aggr(Class(Sum(...

Regards

Marco

Re: How do I group values in a bar chart ?

Anonymous — Sat, 25 Jul 2015 21:09:23 GMT

Dear Maureen,

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

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

Regards,

Neetha

Re: How do I group values in a bar chart ?

MarcoWedel — Mon, 27 Jul 2015 22:29:46 GMT

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

Re: How do I group values in a bar chart ?

Thu, 30 Jul 2015 14:22:22 GMT

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

Re: How do I group values in a bar chart ?

Thu, 30 Jul 2015 16:05:31 GMT

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

Re: How do I group values in a bar chart ?

MarcoWedel — Fri, 07 Aug 2015 22:11:34 GMT

maybe like this:

Displaying empty buckets in class function

hope this helps

regards

Marco

Re: How do I group values in a bar chart ?

MarcoWedel — Fri, 07 Aug 2015 22:13:17 GMT

or by precalculating sequential buckets values for the same field in the script

regards

Marco