Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
If Variance between Actual cost and Standared cost is coming in negative then how to display it (I want to display it as positive)?
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
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
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
Hi
And if i want to highlight that negative valued field with some color then what to do?
yojas
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
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
Thanks for detailed explanation......
yojas
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
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
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.