Datapicta logo
Working with formulas

Sometimes you want to create a chart where the look and feel of the elements depends on the values of the data. For intance, you want to create a bar chart where the color of the bars are green if the value is positive and red if the value is negative.

In DataPicta you can do this by using formulas, in this article we will show you how to do this. We will create a vertical bar chart with positive and negative values. The positive values will be green and the negative values will be red. But we will also use several text elements and image elements that will be placed left or right from the bars, depending on the data.

Introducing the dataset

In this example we will use the dataset of the population of the 27 EU countries. We will use the population of these countries in 2004 and in 2024 and will look at the differences. Some countries have an increasing population, some have a decreasing population. You can find the dataset at eurostat.

We only selected the EU countries and the years 2004 and 2024 and enhanced the dataset with images of the flags of the countries. The resulting dataset can be downloaded as EU population differences. It's only 3kb but that is enough to create an awesome chart.

Import the dataset

  • Go to the DataPicta app
  • Click Data and then click Upload in the dialog
  • Pick the file EU-population-differences.tsv you downloaded earlier
  • Name the dataset EU population differences and click Parse

You will now see the dataset in a table.

Create the bar chart

  • Click on Elements
  • Select Bar from the list of elements

Please note the dataset is already chosen for you, this is because that is the only dataset you have. The type of the bar element is Bar X. This means that the bars will be drawn horizontally. Below that we can select which columns we want to use for this element.

  • For the X axis we will use the column Difference percentage
  • For the Y axis we will use the column EU Country

Let's start with sorting the data. We want the country with the biggest decrease in population at the top and the country with the biggest increase at the bottom.

  • Click on the Style tab and find the Sort section
  • For Sort we choose the Y axis, cause we want to sort the countries on the Y axis
  • For Sort by we choose Difference percentage. This means that the countries will be sorted on the difference in population.
  • Optionally you can choose to sort the data in descending order by clicking the arrow. In this article we leave it as is.

First formula: color the bars

We want to color the bars based on the value of the Difference percentage column. If the value is negative, meaning the population has decreased, we want the bar to be red. If the value is positive, meaning the population has increased, we want the bar to be green.

In every field where you can select a column, you can also enter a formula. We will use a formula in the Fill color field. What does a formula look like? Well, in our case it looks like this:

if (datum['Difference percentage'] > 0) return 'lightgreen';
else return 'lightcoral';

A formula is a piece of code that is executed for every row in the dataset. In our case it will be executed 27 times, once for every country. Inside the formula we can use the datum variable. Note that datum is the singular of data. So datum is a single row in the dataset. We can access the columns of the dataset by using the name of the column in square brackets. In our case we want to access the column Difference percentage.

What we are doing in the formula is checking if the value of the column Difference percentage is greater than 0. If it is, we return the color lightgreen. If it is not, we return the color lightcoral.

These color names are known by every browser. If you want to see all the colors you can use, you can check w3schools color names. If you're familiar with using hex colors you can use them too, for example #FF0000 is red.

  • Copy the formula above and paste it in the Fill color field

Clean up the axes

We will move the X axis to the top of the chart. For the X axis we will also add some grid lines and change the naming of the ticks. About the Y axis, we will remove it completely, because we write the country names inside the chart.

  • Close the Bar element panel by clicking on its header
  • Now add both the X axis and Y axis scale to the chart

The X axis

  • Open the X-axis scale panel by clicking on its header
  • Click the Show grid checkbox to show the grid lines
  • Open the Location drop down and select Top to move the X axis to the top of the chart
  • Click the Common tab
  • Click the Percent checkbox to show the values as percentages
  • Click the tiny menu next to Label and select No label to remove the label of the X axis
  • CLose the X-axis scale panel

The Y axis

  • Open the Y-axis scale panel by clicking on its header
  • Open the Location drop down and select Hide to remove the Y axis from the chart
  • Close the Y-axis scale panel

Add the flags

The data contains a column with the URL of the flag of the country. We will use this column to add these flags to the chart. Flags are images, so we will add an image element to the chart.

  • Click on Elements to add a new element
  • Select Image from the list of elements
  • We will leave the X-axis blank for a moment
  • For the Y-axis we will use the column EU Country
  • For the Url we will use the column flagUrl

All the flags are visible now, but they are all in the center of the chart. But what we want is to place the flags left or right from the bars, depending on the value of the Difference percentage column. For this we will use two formulas, one to place the flags on X=0 instead of the center of the chart, and one to move them to the left or right.

Formula for the X axis

return 0;

That looks simple, but it is important to understand what this does. Remember that formulas are executed for every row in the dataset. And for every row in the dataset we return 0. This means that all the flags will be placed on X=0.

  • Copy the formula above and paste it in the X axis field

Formula for the Url

if (datum['Difference percentage'] > 0) return datum['flagUrl'];
else return '';

Here we are checking if the value of the column Difference percentage is greater than 0. If it is, we return the URL of the flag. If it is not, we return an empty string. This means that the flag will not be shown.

  • Copy the formula above and paste it in the Url field

Now the flags are only shown for the countries with an increasing population. Lets move them a bit to the left.

  • Open the Style tab
  • Scroll down to the Offset section
  • Set the X offset to -16 to move the flags to the left

Now the flags are placed left from the green bars. That looks good, but we still need to place the flags right from the red bars. Well, this process is very similar to the one we just did. Two things are different though:

  • We need a formula that checks if the value of the column Difference percentage is less than 0, not greater than 0.
  • We need to set the X offset to 16 to move the flags to the right, not -16 (to the left).

Can you manage that? Just repeat the steps above with the two minor tweaks, and you will have the flags placed right from the red bars.

Formula for the X axis (decreasing population)

return 0;

Formula for the Url (decreasing population)

if (datum['Difference percentage'] < 0) return datum['flagUrl'];
else return '';

You currently have two image elements, one for the countries with an increasing population and one for the countries with a decreasing population. But both elements are just named Image. This might be confusing, so let's rename them.

  • Click on the pencil icon for the first Image element and rename it to Increasing flags.
  • Click on the pencil icon for the second Image element and rename it to Decreasing flags.

Maybe you want to save the chart now.

  • Click on the Save button in the top right corner and give it a name.

Add the country names

This will be very similar to the flags. But this time we will use a text element instead of an image element.

  • Click on Elements to add a new element
  • Select Text from the list of elements
  • For the X axis we will use the formula return 0
  • For the Y axis we will use the column EU Country
  • For the Text we will use the formula if (datum['Difference percentage'] > 0) return datum['EU Country']; else return '';
  • Click the Style tab
  • Set the Frame anchor to right (THIS IS NEW)
  • Scroll down to the Offset section and set the X offset to -32 to move the text to the left

Now repeat the steps above for the countries with a decreasing population, but this time we will use the formula if (datum['Difference percentage'] < 0) return datum['EU Country']; else return ''; for the Text field, set Frame anchor to left and set the X offset to 32 to move the text to the right.

You might want to rename the text elements too and save the chart again.

Add the percentage differences

This will be very similar to the flags and the country names. But instead of using formula return 0 for the X axis we dont use a formula for the X axis. We will use the column Difference percentage for the X axis so that the text can be placed at the end of the bars.

  • Click on Elements
  • Select Text from the list of elements
  • For the X axis we will use the column Difference percentage
  • For the Y axis we will use the column EU Country

For the Text we will use a formula that also creates a percentage sign.

if (datum['Difference percentage'] > 0) return (Math.round(datum['Difference percentage']*1000)/10)+'%';
else return '';

This formula is a bit more complex. We are checking if the value of the column Difference percentage is greater than 0. To get make a percentage out of the value we should multiply it by 100, but we choose to multiply it by 1000, round the number, and then divide it by 10. So we add one decimal to the percentage.

  • Copy the formula above and paste it in the Text field
  • Click the Style tab
  • Set the Frame anchor to left
  • Scroll down to the Offset section and set the X offset to 4 to move the text to the right

Now repeat the steps above for the countries with a decreasing population, with some minor tweaks. Rename the elements and save the chart again.

Finishing touches

We are almost done. We will now add a title to the chart and add some padding so that the chart is not too close to the edges of the screen.

  • Click on the Canvas element to add a canvas component to the chart
  • Click on the Title and margin in the dialog
  • Set the Title to EU population differences 2004 - 2024
  • Click on the Size and margin button next to Canvas
  • Set the Margin left and Margin right to 50