topic Re: Doubt in column chart in QlikView

Doubt in column chart

Tue, 17 Mar 2015 09:39:48 GMT

hello,

I want to show the data for the unavailable months in bar chart which should be sum(Quantity for available months)/(No. of months),

Please see attached sheet.

Thanks,

swati

Re: Doubt in column chart

vvvvvvizard — Tue, 17 Mar 2015 09:46:34 GMT

ctr + e , select "table files " on bottom screen , choose the excel sheet , click next , next , next .

ctr+r , to load the data in

right click , new chart , choose bar graph ,click next , in the dimension tab add month , click next , in the expression tab add Sum(Quantity)/Count(Month)

click next next next until the end .

Re: Doubt in column chart

arulsettu — Tue, 17 Mar 2015 09:48:35 GMT

like this

Re: Doubt in column chart

vvvvvvizard — Tue, 17 Mar 2015 09:52:20 GMT

Yes that is correct ,

You might just want to sort you months in order , if you right click on the bar chart , properties , sort tab , choose month check box , ascending order , unlclick other checkboxes

Re: Doubt in column chart

Tue, 17 Mar 2015 09:57:42 GMT

I want to show the Quantity

for Oct,Nov and Dec in the same chart after Sep and values for all three (Oct,Nov,Dec) should be sum(Quantity(Jan+Feb+...+Sep)/9) in this case. so, quantity for last 3 months will be avg of first 9 months.

Re: Doubt in column chart

Tue, 17 Mar 2015 10:11:11 GMT

Hi,

load below table:

Load * inline [

Month,	Quantity
Jan,	234
Feb,	45
Mar,	35
Apr,	45
May,	565
Jun,	23
Jul,	12
Aug,	324
Sep,	155

Oct

Nov

Dec

];

try the below expression in your chart.

if(sum(Quantity)=0,sum(TOTAL Quantity)/Count({<Quantity = {'>0'}>} Month),sum(Quantity))

Re: Doubt in column chart

Tue, 17 Mar 2015 11:02:14 GMT

I am still not getting values for last 3 months.

Re: Doubt in column chart

vvvvvvizard — Tue, 17 Mar 2015 11:13:49 GMT

Try if(sum(Quantity)=0,Sum(TOTAL Quantity)/9,sum(Quantity))

Re: Doubt in column chart

Tue, 17 Mar 2015 11:18:38 GMT

By this wat I will get the value but its hard coding which we cannot do, data may change later.

Re: Doubt in column chart

vvvvvvizard — Tue, 17 Mar 2015 11:57:07 GMT

Try

A:
Load * inline [

Month, Quantity
Jan, 234
Feb, 45
Mar, 35
Apr, 45
May, 565
Jun, 23
Jul, 12
Aug, 324
Sep, 155

Oct,

Nov,

Dec,

];

B:
Load *,

if(len(Quantity)>0,Quantity,0) as Counterr
resident A;
drop table A;

Now press CTR+ALT +V and add a variable named

counter2 with expression

=count(if(Counterr>0,Month))

Now add an expression

if(sum(Quantity)=0,Sum(TOTAL Quantity)/$(counter2),sum(Quantity))