Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

variance

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

18 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Hi

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

yojas

Not applicable
Author

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())


1.jpg

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

Not applicable
Author

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

Not applicable
Author

Thanks for detailed explanation......   

yojas

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.