Using Arrows in Bar Chart

    Introduction[editar]

    Arrow Comparing Last Year 1.png

    This article describes how to use arrows to compare data with previous period. This technique is useful when a lot of information needs to be present in a short area. So, chart is displaying four information including total for each dimension value, percentage of total, arrows indicating percentage variation and colors showing a specific group of each bar. If you wanna try create this chart, load the following data sample.

     

    SeaportLoad:

    LOAD * INLINE [

    SEAPORT, FREIGHTAGE, YEAR

    TERMINAL MARÍTIMO DE PONTA DA MADEIRA, 112526506, 2014

    TERMINAL DE TUBARÃO, 109808864, 2014

    SANTOS, 94042814, 2014

    ITAGUAÍ (SEPETIBA), 63849720, 2014

    ALMIRANTE BARROSO, 53110690, 2014

    PARANAGUÁ, 41603425, 2014

    TERMINAL DA ILHA GUAÍBA, 40535106, 2014

    ALMIRANTE MAXIMIANO DA FONSECA, 35058452, 2014

    PONTA DE UBU, 25917564, 2014

    TERMINAL DE TUBARÃO, 110480049, 2013

    TERMINAL MARÍTIMO DE PONTA DA MADEIRA, 107328307, 2013

    SANTOS, 99074788, 2013

    ITAGUAÍ (SEPETIBA), 58327912, 2013

    ALMIRANTE BARROSO, 52807620, 2013

    PARANAGUÁ, 41771840, 2013

    TERMINAL DA ILHA GUAÍBA, 39758214,c 2013

    ALMIRANTE MAXIMIANO DA FONSECA, 25797055, 2013

    PONTA DE UBU, 22683156, 2013

    TERMINAL DE TUBARÃO, 110334522, 2012

    TERMINAL MARÍTIMO DE PONTA DA MADEIRA, 105033621, 2012

    SANTOS, 90737329, 2012

    ITAGUAÍ (SEPETIBA), 57081604, 2012

    ALMIRANTE BARROSO, 50541216, 2012

    PARANAGUÁ, 40441812, 2012

    TERMINAL DA ILHA GUAÍBA, 39818902, 2012

    ALMIRANTE MAXIMIANO DA FONSECA, 37041162, 2012

    PONTA DE UBU, 23512589, 2012];

     

    While this chart is similar a Bar, in fact it's a combo chart where lines are hidden. Three expressions are used to create this scenario. First one is used to calculate each bar size by seaport. Another one is included to show each arrow comparing actual year versus previous year. At last, more one expression is defining percentage distribution over total, showed at bar seed. To compare actual year versus previous year, is necessary use SETANALYSIS technique. SETANALYSIS isn't covered in this item. For more information about SETANALYSIS, search it at www.qlik.com.

     

    Creating a Combo Chart[editar]

    After load previous data, create a new combo chart using SEAPORT as dimension. Next, enter a first expression formula:

    =Sum({$<YEAR={'$(=Max(YEAR))'}>} FREIGHTAGE) 

    This formula is used to get only values for maximum year.

    ArrowLastYearEnglish1.png

    Change some properties to improve your chart design.

    • In Expression tab, select Values on Data Points for your first expression.
    • Enter a Label FREIGHTAGE for your expression.
    • Open the FREIGHTAGE expression properties and enter a following formula to change Background Color.
    • Background color is going to be used to evaluate the transportation's volume.

     

    =IF(Sum({$<YEAR={'$(=Max(YEAR))'}>} FREIGHTAGE) > 100000000, RGB(106, 132, 191), RGB(172, 183, 219))

    BorderWith1English.PNG

    • In FREIGHTAGE expression properties change Text Color definition to =White().
    • Click Next twice up to Style tab. Change orientation to landscape (horizontal).
    • Click Next more one time and select Plot Values Inside Segments.
    • In Cluster Distance change value to 3.
    • In Axes tab (next one) check Hide Axis.
    • Format FREIGHTAGE expression using Number format in Number tab.
    • In Layout tab change Border With to 0pt.
    • In last tab Caption uncheck Show Caption.

     

    Arrows Comparing Years[editar]

    Create a new expression and use Dual function to calculate a position of each arrow. Dual will be used to show arrows based on FREIGHTAGE value. The expression formula is shown below:

    =Dual(Num((Sum({$<YEAR={'$(=Max(YEAR))'}>} FREIGHTAGE) - Sum({$<YEAR={'$(=Max(YEAR)-1)'}>} FREIGHTAGE)) / Sum({$<YEAR={'$(=Max(YEAR)-1)'}>} FREIGHTAGE), '▲ #.##0,00%; ▼ -#.##0,00%'), Sum({$<YEAR={'$(=Max(YEAR))'}>} FREIGHTAGE)) 

    This formula means (ACTUAL YEAR - PREVIOUS YEAR) / PREVIOUS_YEAR. Result is a variation percentage from actual year versus previous year. For this new expression change some properties as:

    ArrowLastYearEnglish3.png

    • On new expression uncheck both bar and Symbol in Expressions tab.
    • Make sure Line is checked.
    • Select (check) Values on Data Points.
    • Select (check) Invisible option.
    • Change Label for new expression as ARROW.
    • Enter a formula for Text Color property as:

     

    =IF((Sum({$<YEAR={'$(=Max(YEAR))'}>} FREIGHTAGE) - Sum({$<YEAR={'$(=Max(YEAR)-1)'}>} FREIGHTAGE)) / Sum({$<YEAR={'$(=Max(YEAR)-1)'}>} FREIGHTAGE) > 0, Green(), Red()) 

     

    Percentage Over Total[editar]

    Create a new expression to show seaports with more transportation volume in actual year. Use the following formula:

    =Num(Sum({$<YEAR={'$(=Max(YEAR))'}>} FREIGHTAGE) / Sum({$<YEAR={'$(=Max(YEAR))'}>} TOTAL FREIGHTAGE), '0%') 

    Change some properties for this new expression as:

    • In Expressions tab change Label to % OVER TOTAL.
    • For % OVER TOTAL expression, uncheck all options, unless Text on Axis that should be selected.
    • In % OVER TOTAL properties, set =black() as Text Color.

    For this chart isn't necessary show legend, so access chart properties and select Presentation tab. Under Legend option, clear Show Legend. Access General tab and clear the option Show Title in Chart. From Dimension tab, clear the Label option. When you finish your job, Chart would be something like below.


    ArrowLastYearEnglish5.png

     

    Need more?

    www.qknow.com.br