topic Re: variance in QlikView

variance

Sun, 15 Apr 2012 16:07:19 GMT

If Variance between Actual cost and Standared cost is coming in negative then how to display it (I want to display it as positive)?

variance

Sun, 15 Apr 2012 16:25:47 GMT

Hi yojassamarth

Try using the fabs() function around your expression. The result is the size of the number - ie the number as a positive.

Regards

Erica

Re: variance

Sun, 15 Apr 2012 16:53:06 GMT

Hi Yojas,

As Erica said fabs() function will always return absolute (unsigned) value of the expression passed as parameter.

So, if you want absolute value of your variance than pass your expression to fabs function.

So, if you write fabs(Absolute Cost - Standard Cost) then it will return only value without its sign (absolute value). Also the same value will be returned if you write it otherwise fabs(Standard Cost - Absolute Cost).

Warm Regards,

Ashutosh

variance

Sun, 15 Apr 2012 17:38:58 GMT

And if i want to highlight that negative valued field with some color then what to do?

yojas

Re: variance

Sun, 15 Apr 2012 17:53:20 GMT

Hi,

This can be done using text color for your expression.

Normally, You can use your expression name or can also use column() function so for example if your expression is column(1) then you can put if (column(1) < 0, red(), black())

or same way if your expression name is variance then you can put if (variance<0,red(),black()) .

Qlikview by default shows column names in blue color.

The same effect can also be achieved through Visual cues tab and you can put your conditions (select your column and put < 0 and choose your color) there also.

But here you will not be able to use Column() function or your expression name to refer to the expression because that will return absolute value (no negative value) which will always be greater than 0.

Also, You can not use visual cues because they works on the column values directly and you will never have any negative values in your variance expression column, since you are using fabs().

So you have to use text color in your expression (click on +on your expression) and put the same expression you are using in variance column without fabs() in the text color definition.

So, if your variance expression is fabs([Actual Cost] - [Standard Cost]) then in text color definition put

if(([Actual Cost] - [Standard Cost]) < 0, red(), black())

I have tried to explain it in a little detail and I am worried that it can be confusing (bad writing skills ).

So, Please let me know if any thing remains confusing.

Thanks and Regards,

Ashutosh

variance

Sun, 15 Apr 2012 19:26:40 GMT

Also, red() and Black() I have here used just for explanation, you can use any other color function as well as rgb and argb functions.

Thanks

Ashutosh

variance

Mon, 16 Apr 2012 01:49:29 GMT

Thanks for detailed explanation......

yojas

variance

Mon, 16 Apr 2012 01:58:03 GMT

i used if(actual cost-standard cost<0,red(),green()) and i am getting output correctly... but my column name becomes green after aplying this expression.... but i want it to be in black.... what to do?

yojas

variance

Mon, 16 Apr 2012 03:42:43 GMT

And if i want to hihglight negative values on bar charts or line charts then what to do? writting on color text is not working....

yojas

Re: variance

Mon, 16 Apr 2012 05:36:51 GMT

Hi,

Yes, this was a long standing bug that if you use text color (This does not happen when you use visual cues) in table charts then the color which comes for the first row also comes for the label but it has been rectified in version 11.

As, I am attaching the file here in which you can see it. (May be if you open in version 10, you see the color but in verison 11 there is black color for the label).

Also, in attached file, I have used text color only but it is showing the different colors on bars. (check values on data points)

I hope that I am not missing anything, Please let me know if any issue.

Re: variance

Mon, 16 Apr 2012 05:51:55 GMT

Also, I would suggest to download Qlikview 11 as SR 1 has also been made available.

variance

Mon, 16 Apr 2012 05:59:34 GMT

hmm... Thanks.....

yojas

variance

Mon, 16 Apr 2012 09:41:39 GMT

i applied text color option on two diferent expressions ... for one field i am getting exact o/p as i want means the title is in black color.....but for second expression i am getting green color for heading why it is so??? as i am appling same thing for both exp. .... please help me out for this issue......

yojas

Re: variance

Mon, 16 Apr 2012 09:45:27 GMT

Hi,

What is the text color of the first row for both the expression?

or, if you can share your application!!

Thanks,

Ashutosh

variance

Mon, 16 Apr 2012 09:50:22 GMT

i used below expression for both the tables....but i am getting different o/p for both... how could it possible???

if(Field_Name<0,red(),green())

yojas

Re: variance

Mon, 16 Apr 2012 09:50:39 GMT

i used below expression for both the tables....but i am getting different o/p for both... how could it possible???

if(Field_Name<0,red(),green())

yojas

variance

Mon, 16 Apr 2012 09:56:34 GMT

Hi,

Are both of the tables straight tables ? or One of them is Pivot table?

Reagards,

Ashutosh

variance

Mon, 16 Apr 2012 09:58:23 GMT

Can you share the application, if is not too big in size?

variance

Mon, 16 Apr 2012 16:41:14 GMT

And, I believe that I somehow got confused at that time when I said that label color behaves differently in version 11 compared to earlier verisons, because I just now checked and it was same behavior for v 10 and 11.

But yes, label color was being changed to first row color in Pivot tables but in Straight tables it was consistent, and I believe I got confused that way only.

So, please accept my apologies for that.

And, If you are encountering this behavior in straight table then please let me see, even if you can give some sample app that will also do fine , because I am not able to regenerate this behavior on my system.

If I can see it then It will help me also to learn it better.

As you see in this image, Pivot table (2nd) labels are green while straight table (1st) labels are black.

Thanks and Best Regards,

Ashutosh