Show negative values on Charts

    Hi,

     

    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

     

    LOAD * INLINE [
    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

    Untitled.jpg

     

     

    see also,

    Control Chart

    Benford's law

    Bollinger Bands

    Rolling N months

     

    Regards,

    Kushal