Go to Editor

Tutorials - Getting Started

How to make Formulas

Your First Minutes With Quadrigram: Using Formulas

You can modify and select specific bits of data by using the Formula section underneath file. The Formula allows you to create specific blocks unique for the chart you want to create.

Formulas are recognized by the sigma (Σ) sign in the data station. To create a formula click on the Formulas tab and select on the Create new formula. Once you click you will notice a new toolbar will appear underneath the data station and should look something like this:

The default formula is set to filter but you can change this by clicking on the sigma sign (Σ) or down-pointed arrow which brings up the drop-down menu.

There are three types from the drop-down menu that make formulas especially useful:

Aggregate

The aggregate function is mainly for aggregating repeated categorical values in a column. If you have a spreadsheet where a name in a column is repeated, it will aggregate these into rows based on the function you give it. This helps organize your data and gives you greater flexibility in deciding what you want to show with your information. There are various functions to choose from:

  • By adding: adds the numeric value of names repeated in a column. For instance, if you are looking at sales of companies in a spreadsheet and a company's name is repeated in a column, it will add all the sales of the company that is repeated into a single row.
  • As the mean: this calculates the average of names repeated in a column. The average of numeric values repeated is calculated into a single row.
  • By counting: choosing the column you want to analyze, it will tell you the number of times that column is repeated.
  • Minimum Value: orders from lowest value (least) to highest value (greatest).
  • Maximum Value: orders from highest value (greatest) to lowest value (least).
  • First Value: show first value.
  • Last Value: show last value.
  • Ignore: ignore assigned value.

You aggregate based on the column selected. To select the column you wish to aggregate go to the formula bar and choose the column from the drop-down menu after it says repetitions of.

Aggregate Formula has three slots:

  1. For adding your File Data Block.
  2. Is for deciding the Function.
  3. Is deciding the Column for aggregating values.

It should look like this:

You can review the information by double clicking on the formula building block, which should look like this:

Upon double clicking you can see the information calculated in the block:

You can now use this data block to add to chart type to visualize your information.

Pivot Tables

Another formula you can use in Quadrigram is Pivot Tables. This type of formula is effective for moving around data between rows and column and is a great way to summarize, analyze, and explore the information in your dataset. You can use this to visualize information with your formula block.

Pivot Tables are made up of three components:

  1. Column fields: which go on top and is categorical value.
  2. Row fields: which take up the first column and is categorical value.
  3. Data fields: which is the data between column and row and is always numerical value.

This appear in the formula toolbar as follows:

Pivot Tables Formula has five slots:

  1. For adding your File Data Block / your spreadsheet.
  2. To decide what column you will use based on the added spreadsheet.
  3. To decide what row you will use based on the spreadsheet.
  4. To decide what column you will aggregate data.
  5. Is for deciding the Function for aggregation.

It should look like this:

You can review the information by double clicking on the formula building block, which should look like this:

Upon double clicking you can see the information calculated in the block:

You can now use this data block to add to chart type to visualize your information.

Filter

Filter function allows you to add conditions that give you the power to filter information. You do this by creating a new formula. As will all formulas, you begin by adding your File Data Block / spreadsheet to the first slot. You can select the information you want to filter by selecting the column from the drop-down menu in the second slot.

The fourth slot in the formula is where you manually add the value you want to filter.

Once you have selected the column you have to choose the function. There are various functions to choose from:

  • Equal to: the value you add in the fourth slot (numerical).
  • Greater than: is greater than the value you add in the fourth slot (numerical).
  • Greater or equal than: value is greater or equal to value added (numerical).
  • Smaller than: value is less than what is added (numerical).
  • Smaller or equal: value is less or equal to value added (numerical).
  • Different from: value is different from value added (categorical).
  • Contains: value contains categorical value written as.
  • Doesn't contain: value does not contain categorical value written as.

You can continue to add conditions to your filter formula by pressing the plus (+) at the end of the formula.

Your filter formula should look like this: