Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to make a Histogram chart with the given data?

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

5 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

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'

Not applicable
Author

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?