Using Arrows in Bar Chart (English)

De Qknow
Ir para: navegação, pesquisa

Introduction

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 at side 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.

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.


Try create this chart use the following data:

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];

Creating a Combo Chart

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

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

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


Functions Used

  • SUM
  • DUAL
  • RGB
  • MAX
  • IF
  • NUM

Resources


Idea 1.jpg

Learn to use variables to replace hard expressions.

Variables can get parameters to change its formulas. Learn more here!


Envelope01.jpg
Need more? Mail Us!



Back | Summary | Main Page