Tableau can be used in many situations to measure key performance indicators (KPIs). Most commonly KPIs are comparisons to another measure such as a target/goal or previous period that indicates whether a business is excelling or falling behind. In this post I'm going to explain how you can add colored arrows to indicate positive, negative, or equal comparisons.

Click this link to view and download the visualization from Tableau Public.

This example does not use a dual axis although that is another way to accomplish the same task.

Using the Tableau Superstore data this visualization shows the sales for each year down to the Product Sub-Category level. The background of the cells indicates the amount of sales. The arrow denotes if the sales increase or decreased by more than the parameter amount or stayed within the parameter amount year over year.

In order to create these flags I had to write 3 calculations (one for each arrow type). If you do not write 3 separate calculations you will not be able to assign different colors to each arrow.

1. Here is the calculation for the decrease arrows:

IF (LOOKUP(sum([Sales]),-1)-[Increase/Decrease Amount]) > sum([Sales]) THEN "▼" END

This calculation looks up the previous years sales and determines if the current cell's sales are greater than the previous year plus the parameter amount or not. If the current cell's sales are less than the previous year plus the parameter amount it will assign a down arrow (▼). If the current cell's sales greater than the previous year plus the parameter amount nothing will happen (null).

The increase arrow calculation will be the opposite of the decrease arrow calculation. 2.

2. Here is the calculation for the "equal" arrow:

IF ISNULL(LOOKUP(sum([Sales]),-1)) THEN null

ELSEIF ISNULL([Increase in Sales (Arrow)]) AND ISNULL([Decrease in Sales (Arrow)]) THEN "►"END

This calculation looks to see if the previous cell's data is null. This happens if the cell is showing the first year of data. When the cell is showing the first year of data nothing happens (null). The second part of the calculation is if there is no increase or decrease arrow then show the equal arrow (►).

3. Once all three arrows are created they are added to the label of the cross tab.

4. Edit the label. Put all of the arrows onto the same line without any spaces between them. Color each measure to match the arrow color. <AGG(Decrease in Sales (Arrow))><AGG(Increase in Sales (Arrow))><AGG(Equal Sales)> <SUM(Sales)>

This example does not use a dual axis although that is another way to accomplish the same task.

Using the Tableau Superstore data this visualization shows the sales for each year down to the Product Sub-Category level. The background of the cells indicates the amount of sales. The arrow denotes if the sales increase or decreased by more than the parameter amount or stayed within the parameter amount year over year.

In order to create these flags I had to write 3 calculations (one for each arrow type). If you do not write 3 separate calculations you will not be able to assign different colors to each arrow.

1. Here is the calculation for the decrease arrows:

IF (LOOKUP(sum([Sales]),-1)-[Increase/Decrease Amount]) > sum([Sales]) THEN "▼" END

This calculation looks up the previous years sales and determines if the current cell's sales are greater than the previous year plus the parameter amount or not. If the current cell's sales are less than the previous year plus the parameter amount it will assign a down arrow (▼). If the current cell's sales greater than the previous year plus the parameter amount nothing will happen (null).

The increase arrow calculation will be the opposite of the decrease arrow calculation. 2.

2. Here is the calculation for the "equal" arrow:

IF ISNULL(LOOKUP(sum([Sales]),-1)) THEN null

ELSEIF ISNULL([Increase in Sales (Arrow)]) AND ISNULL([Decrease in Sales (Arrow)]) THEN "►"END

This calculation looks to see if the previous cell's data is null. This happens if the cell is showing the first year of data. When the cell is showing the first year of data nothing happens (null). The second part of the calculation is if there is no increase or decrease arrow then show the equal arrow (►).

3. Once all three arrows are created they are added to the label of the cross tab.

4. Edit the label. Put all of the arrows onto the same line without any spaces between them. Color each measure to match the arrow color. <AGG(Decrease in Sales (Arrow))><AGG(Increase in Sales (Arrow))><AGG(Equal Sales)> <SUM(Sales)>

amazing

ReplyDeleteThis comment has been removed by a blog administrator.

ReplyDeleteThis comment has been removed by a blog administrator.

ReplyDelete