Introduction[editar]

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.

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))

• 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:

• 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 Legendoption, clear Show Legend. Access General tab and clear the option Show Title in Chart. From Dimensiontab, clear the Label option. When you finish your job, Chart would be something like below.