Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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'
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?