Show negative values on Charts



    I have seen many posts on community for showing negative values on charts like Block chart, pie chart etc. There is a solution available on community, but I thought to create the document so that everyone can utilize the same.


    Lets say I have below data set


    Country, Score
    US, 4971.765
    UK, 4782.54
    Japan, -200.941
    India, 1412.181
    Russia, 2445.292


    Now I want to create the Block chart with above data set. So create the block chart with dimension as country and expression as sum(Score) , as soon as you create the chart you will get the error message that "positive and negative values in chart". But you can still display the negative values on chart with slight modification in expression.


    Steps to create the block chart with negative value


    1) Create the block chart

       Dimension : Country

       Expression : dual(sum(Score),fabs(sum(Score)))

    If you want to display the distribution in % then use below expression

    dual(num(sum(Score)/sum(TOTAL Score),'#0.00%'),fabs(sum(Score)))


    Using dual function you can display the negative value with the base as positive value.


    2) Go to chart properties -> Number Tab and check the Number format setting always as "Expression default". If the number format setting is not the expression default then negative values will be displayed as positive and also data will be displayed in wrong format. So if you want to provide any format like %, money you can define in the Dual expression itself using num function


    Please see the attached application. In this application I have used the Fast change option so that you can see the results for multiple chart type as well




    see also,

    Control Chart

    Benford's law

    Bollinger Bands

    Rolling N months