topic How to put both -ve and +ve value in same bar chart in QlikView

How to put both -ve and +ve value in same bar chart

Thu, 12 Nov 2009 18:07:09 GMT

In a column of a table if contains both -ve and +ve value how we can put both -ve and +ve value in bar chart.

How to put both -ve and +ve value in same bar chart

prieper — Sun, 15 Nov 2009 01:53:49 GMT

A bar-chart should have no problem to display -ve and +ve values, in a pie-chart you may have a problem.

Peter

How to put both -ve and +ve value in same bar chart

Mon, 16 Nov 2009 12:54:26 GMT

I am displaying Yearly SalesValue in one bar and wants to display the variance of sales(difference in salesvalue when compared to last year) in another bar. But i am unable to display the bar (variance of sales ) which contains both +ve and -ve values. Please help me out.

How to put both -ve and +ve value in same bar chart

shumailh — Mon, 16 Nov 2009 13:54:22 GMT

I think the variance you have in your data is very minor that's why you can't be able to see it in a graph, I have designed the same senario in the attachement,

Regards,
Shumail Hussain

How to put both -ve and +ve value in same bar chart

shumailh — Mon, 16 Nov 2009 13:58:28 GMT

graph will be showing like this, if the values are smaller in variance field then you require to work on the design of the graph.

Regards,
Shumail Hussain

How to put both -ve and +ve value in same bar chart

Mon, 16 Nov 2009 14:34:58 GMT

Variance value are larger enough to be displayed as bar and infact it is larger than the sales value which are displayed as a bar.

How to put both -ve and +ve value in same bar chart

shumailh — Mon, 16 Nov 2009 14:52:29 GMT

Have you tried the attachement which i shared earlier? are you still facing problem with the garph if then can you share your graph?

Regards,
Shumail Hussain

How to put both -ve and +ve value in same bar chart

Mon, 16 Nov 2009 16:10:10 GMT

I am using personal edition so will not be able to open your application. One more thing is that when i don't add the variance field in the expression then sales label is getting displayed in the chart or else it is not getting displayed.

How to put both -ve and +ve value in same bar chart

shumailh — Mon, 16 Nov 2009 17:10:39 GMT

To hide/unhide label use the below property.

How to put both -ve and +ve value in same bar chart

Mon, 16 Nov 2009 17:29:12 GMT

I knew that i was just sharing you my personal scenario here. By the way in expression SalesValue: Sum (SalValue) and the Variance: (Sum(SalValue) - before(Sum(SalValue))) Hope you can get some idea where i am going wrong.

How to put both -ve and +ve value in same bar chart

shumailh — Mon, 16 Nov 2009 21:15:13 GMT

reference from the Qlikview Help:

before( [ total ] expression [ , offset [,n ]] )

Returns the value of expression evaluated with a pivot table's dimension values as they appear in the column before the current column within a row segment in the pivot table. This function returns NULL in all chart types except pivot tables.

On the first column of a row segment a NULL value will be returned, as there is no column before this one.

How to put both -ve and +ve value in same bar chart

shumailh — Mon, 16 Nov 2009 21:17:24 GMT

I think, you need to define variance on edit script mode.

Regards,
Shumail Hussain

How to put both -ve and +ve value in same bar chart

Mon, 16 Nov 2009 23:27:05 GMT

In a bar chart you might be able to use the Above() function rather tham Before()

EG:

Sum(SalValue) - Above(Sum(SalValue),0,1)

How to put both -ve and +ve value in same bar chart

Mon, 16 Nov 2009 23:35:19 GMT

You could probably use the Above() function in a bar chart to do this.

Try something like:

Sum(SalValue) - Above(Sum(SalValue),0,1)

How to put both -ve and +ve value in same bar chart

Tue, 17 Nov 2009 11:47:48 GMT

Hello Nigel! I used Above() function it doesn't work either. Please let me know whether there is any alternate for this.

How to put both -ve and +ve value in same bar chart

Tue, 17 Nov 2009 12:13:25 GMT

Hello Hussain! Let me know what and how i need to define in edit script mode.

How to put both -ve and +ve value in same bar chart

shumailh — Tue, 17 Nov 2009 13:17:18 GMT

you can use the same function which you are using at the development level. add this field in your script and load


LOAD Year,
 Sales,
 (Sum(Sales) - before(Sum(Sales))) as Variance
FROM  (biff, embedded labels, table is Sheet1$);

Regards
Shumail

How to put both -ve and +ve value in same bar chart

shumailh — Tue, 17 Nov 2009 13:21:18 GMT

Above() function also returns null in all chart type.. reference from the help.

Regards,
Shumail Hussain

How to put both -ve and +ve value in same bar chart

Tue, 17 Nov 2009 13:37:23 GMT

Hello Hussain! I have tried your method but it says that BEFORE is not a valid function. Please error message which i got when i loaded script.

Error in expression:
BEFORE is not a valid function

Variance:
LOAD Year,
Sales,
(Sum(Sales) - before(Sum(Sales))) as Variance
FROM stk_sales

How to put both -ve and +ve value in same bar chart

shumailh — Tue, 17 Nov 2009 13:55:55 GMT

This will works,


LOAD Year,
 Sales,
 Sales - Previous(Sales) as Variance
FROM  (biff, embedded labels, table is [Sales$]);

Regards,
Shumail Hussain