topic Re: How to make a Histogram chart with the given data? in QlikView

How to make a Histogram chart with the given data?

Sun, 10 Jun 2012 18:23:48 GMT

Hi All,

My data is in this format:

Column1(Country) Column2(Product) Column3(Netsales)

A Dove 250

A Sunsilk 100

B Panteen 300

and the list goes on, now what i want is to plot a histogram chart.

Whenever i select a country i should get all the products under that country as bar where each bar represents a product and the BIN should be below avg, avg and above average.

Please reply if anyone has any idea, if possible tell me the expressions and dimenssions as i don't have Licensed version of QV.

Many thanks in Advance

Thanks and Regards

Sagar Gupta

How to make a Histogram chart with the given data?

swuehl — Sun, 10 Jun 2012 22:24:30 GMT

I haven't fully understood what you mean with '...all products under that country as bar where each bar represents a product and the BIN should be below avg, avg and above average.', i.e. showing all countries and showing bins seems to me a bit controversial.

Maybe you could post the expected outcome for the above, or a somewhat more complex, sample data (as hand-drawing, excel table etc.)

Regards,

Stefan

Re: How to make a Histogram chart with the given data?

Mon, 11 Jun 2012 03:22:27 GMT

Thanks for the Reply

Here is what i want, say 1 have 10 counties and each country has 20 products(May be distinct or may not be), now if i select a country from the list box i should get a histogram which consist of products which fall in the selected country(represented as bars) and my X axis should have three buckets:

1: Below average(all prducts whose sales is below average, say: 0-120)

2: Average( all products whose sales are average, say: 120-300)

3: Above average(all products whose sales is above average, say: 300+)

Hope this will make you clear about my requirement.

Thanks a lot again for replying

Regards

Sagar Gupta

Re: How to make a Histogram chart with the given data?

swuehl — Mon, 11 Jun 2012 07:34:27 GMT

Sagar Gupta,

you could create a calculated dimension for your bar chart, something like

=aggr(

if(sum(Sales) <= avg(total aggr(sum(Sales),Product))-$(vRangeAvg),'Below avg -$(vRangeAvg)'

,if(sum(Sales) <= avg(total aggr(sum(Sales),Product))+$(vRangeAvg),'avg +-$(vRangeAvg)'

,'above avg+$(vRangeAvg)')),

Product)

and as expression in your bar chart:

=count(distinct Product)

The calculated dimension is grouping your products into 3 bins.

I created an input box with a variable vRangeAvg to control the width of the average+-X bin.

You could also create something similar in the chart, but if you need to be selection sensitive (i.e. selection on Products / products group), I would go for the calculated dimension.

I know that you can't open the file, but maybe anybody elser want to see a sample, so I am attaching one.

Regards,

Stefan

Re: How to make a Histogram chart with the given data?

Fri, 15 Jun 2012 00:27:18 GMT

Hi, I think if you using the Class() function will help make this alot easier. Here is a quick copy/paste from the manual:

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'

Re: How to make a Histogram chart with the given data?

Mon, 02 Feb 2015 15:25:43 GMT

I am trying to use your examlpe to solve my problem but I can do it.

I have data (Diferença) between some value and I want to count the value between 0-1, 1-2, 2-3 and 3 or greater.

I used this code to the calculated dimension:

=Aggr(if(Diferença<1, '0-1',

if(Diferença>1 and Diferença<=2, '1-2',

if(Diferença>2 and Diferença<=3, '2-3',

If(Diferença>3, '>3')))),Diferença).

On the chart it says 'No numeric values on x-axis'. Can someone help me?