# Introduction
This guide will help you to build visualizations for Trello using [**Vizydrop**](https://trello.vizydrop.com) — a business intelligence platform that was created to provide users a natural way of exploring data, while staying as easy-to-use as Trello itself.
Our power-up for Trello contains a gallery of [**predefined templates**](https://trello-guide.vizydrop.com/create-chart-from-template.html). We've included charts that are popular among Trello users, so you can get started with a ready-to-go solution without wasting time on understanding the tool from scratch.
Keep in mind that you can always modify any of your existing charts, or choose to build a visualization from scratch using our intuitive [**chart editor**](https://trello-guide.vizydrop.com/using-chart-builder.html), which provides users with an efficient and simple way of exploring data. We support most common chart types.
We've also created our own visualization library, [**Tauсharts**](https://taucharts.com) (based on the D3 framework and 'Grammar of Graphics' concepts), in order to support advanced visualization techniques — such as facets — with minimal effort.
[**Share**](https://trello-guide.vizydrop.com/sharing.html) your visualizations with colleagues, friends, your mom, and the whole world using our platform.
Good luck with this new visual way of exploring Trello card data exploration and visualizing numbers.
Thank you for using our platform,
— The Vizydrop Team
# Getting Started
To start using Vizydrop Charts, you simply need to enable our Power-Up for Trello boards.
**1.**Open board **settings** and navigate to Power-Ups.
**2.** Find **Charts** in the list of Power-Ups and enable it.
**3.** A **Charts** **link** will appear in right upper corner of board after enabling Power-Up. **Click** on it to start using Vizydrop Charts.
**4.** You will be asked to grant access to Vizydrop to read data from your boards. Click **Allow** to proceed.
# Chart Templates
## Template Selection on New Report Page
After you have enabled the Charts Power-Up and open the Charts Link from your board, you are redirected to **New Report** automatically. You can also navigate to this page from Your Charts area by clicking the Add Chart button. Find the template you are interested in by keywords or tags, and click on it to proceed.
## Source Configuration Popup
A Source Configuration popup is shown right after the templates selection. It provides the possibility to configure boards you want to explore visually, and a date range for historical data. Change the source settings if this is required, and click the **Create Chart** button.
## Chart Editor
Once data processing is complete, the chart editor will appear. You can modify the default chart settings applied by platform, or simply finalize chart creation by pressing the **Finish Setup** button.
# Your Charts Area
The 'Your Charts' area contains all visualizations created by you or your teammates. Click on a chart to see it in full detail. Charts can be reordered using drag and drop. Click **Add Chart** to build a new chart from a template or from scratch.
# Chart Editor
## Anatomy & Terminology
You can find an explanation of basic elements in the chart editor via the image below:
## Source Settings
Source settings shows the configuration of chart data source. Can be changed by pressing Edit Data Source button. Please note source type can be changed only for new report. We have created two source types for Trello: **Cards** and **History of cards**. Cards source is consists of your current or archived board cards and considered to be used when you suppose to visualize data related to current state of board, for example cards distribution of lists. History of cards consists of transitions between lists. Every raw in datasets of this type is a historical action related to moving card from one list to another. It's considered to be used for visualizing list changes of cards during time in history, for example cumulative, durations or transitions reports.
## Fields Catalog
Fields catalog contains all fields available for report datasource. Use drag and drop to put field into destination axis or double click on field and system will put the field into some suitable axis automatically. Description can be explored by putting mouse over interested field. Fields can be hidden from catalog by clicking red cross which is appeared on mouse over and can be visible again by using Add Field button.
## Name & Description
You can change the name and the explanation of the chart by providing values in name and description text boxes.
## Dimensions
The data field which is put into any axis or color, size and label panels using drag and drop becomes dimension. You can create dimension for axis or color and other panels by using axis "+" button using creating new calculation popup.
Dimension popup contains calculation of the dimension, link to chart filters, group buttons or aggregation options which should be applied to calculation. Click in calculation text box will lead to calculation popup so you can edit to create complex calculation which can not be handled by UI of the system.
here are some visual settings like axis title or annotations. Annotation line can be added by pressing Add new button under Annotation Lines section. Scale for dates can be switched to periodic rendering.
## Chart Type
We support six types of graphical representation of data: Scatterplot, Bar, Stacked Bar, Area, Stacked Area and Lines. Click on chart type icons to find desired one.
## Data Source Panel & Filters
Income data can be browsed and filtered by using data panel and filters. Click on Browse button and you will see data panel consists of two tabs. **Chart Data** contains aggregated and calculated data on chart and **Source Data** shows raw income data set.
Data paned contains **Export To CSV** button and filter controls which allow to exclude non-required values or focus on some specific subset of data.
## Color, Size & Label Panels
Drop fields into Color and Size panels when you need to encode your chart elements by color or size.
Create calculation or drop field into Label Panel when it is required to split dataset by groups and sign them with categorical values.
## Trendline and Interpolation
It is almost always interesting to know the general trend of a variable. Trendline panel contains options: **Linear and Exponential**.
Interpolation can be used for making elements smoother or turned into steps (can be applied for area and line types). You can change places of X and Y dimensions by pressing **Reverse Axis** button.
# Calculations
Calculations allow us to use much more powerful techniques for data manipulation. Quite often, configuring a chart will require you to apply some aggregations and post-aggregations to your data field. This can be done using in-chart calculations. Technically every data field which is put into an axis or encoding panel (e.g. color) has a calculation. Some dimension calculations can be created using UI aggregation and grouping buttons, but some can only be created using the calculation text box.
## How calculations can be added or edited
New calculations can be added using the "+" sign, or by double-clicking x, y, color, size, or label. The "New Calculation" popup will appear. It utilizes autocomplete and is charged with samples containing almost every available function.
You can find the list of available functions and operators below.
## Numerical Calculations
#### +, -, \*, /
Basic math functions.\
*Sample: 2\*3 - 1 + 5/2*
#### SUM(numerical expression)
Returns the sum of a series of numbers.\
*Sample: SUM(\[Effort])*
#### SUMIF(numerical expression, condition)
Returns the conditional sum of a series of numbers.\
*Sample: SUMIF(\[Effort], \[Board] == 'Me')*
#### AVG(numerical expression)
Returns the average of a series of numbers.\
*Sample: AVG(\[Effort])*
#### AVGIF(numerical expression, condition)
Returns the conditional average of a series of numbers.\
*Sample: AVGIF(\[Effort], \[Board] == 'Super')*
#### MIN(numerical expression)
Returns the minimum of a series of numbers.\
*Sample: MIN(\[Effort])*
#### MINIF(numerical expression, condition)
Returns the conditional minimum of a series of numbers.\
*Sample: MINIF(\[Effort], \[Board] == 'Me')*
#### MAX(numerical expression)
Returns the maximum of a series of numbers.\
*Sample: MAX(\[Effort])*
#### MAXIF(numerical expression, condition)
Returns the conditional maximum of a series of numbers.\
*Sample: MAXIF(\[Effort], \[Board] == 'Targetprocess')*
#### COUNT(field)
Returns the count of rows within a series.\
*Sample: COUNT(\[Id])*
#### COUNTIF(field, condition)
Returns the conditional count of rows within a series.\
*Sample: COUNTIF(\[Id], \[Board] == 'Targetprocess')*
#### COUNT\_DISTINCT(field)
Returns the count of distinct (different) field values within a series.\
*Sample: COUNT\_DISTINCT(\[Name])*
#### COUNTIF\_DISTINCT(field, condition)
Returns the conditional count of distinct (different) field values within a series.\
*Sample: COUNTIF\_DISTINCT(\[Name], \[Board] == 'Targetprocess')*
#### TOTAL(aggregation(numerical expression))
Returns the total for a given expression across the whole dataset.\
*Sample: TOTAL(SUM(\[Effort]))*
#### RUNNING\_SUM(aggregation(numerical expression))
Returns the running sum for a given expression from the first row to the current.\
*Sample: RUNNING\_SUM(COUNT(\[Id])) gives cumulative sum by groups defined for chart*
#### RUNNING\_AVG(aggregation(numerical expression))
Returns the running average for a given expression from the first row to the current.\
*Sample: RUNNING\_AVG(COUNT(\[Id]))*\
RUNNING\_AVGcalculation\
**(prevValue - prevValue / count) + (currentValue / count)**\
for ex., count values is 5, 35, 25\
first value is 5\
for second element, calculation is (5-5/2) + (35/2) = 20\
for third element (take calculated number for second element value), calculation is (20-20/3)+25/3 = 21.67
#### RUNNING\_MIN(aggregation(numerical expression))
Returns the running min for the given expression from the first row to the current.\
*Sample: RUNNING\_MIN(SUM(\[Effort]))*
#### RUNNING\_MAX(aggregation(numerical expression))
Returns the running max for the given expression from the first row to the current.\
*Sample: RUNNING\_MAX(SUM(\[Effort]))*
#### DIFFERENCE(aggregation(numerical expression))
Returns the running difference for the given expression between current row and previous.\
*Sample: DIFFERENCE(COUNT(\[Id]))* \
DIFFERENCE calculation is **(current value - previous value)**\
For example, if we have calculation based on cards with MONTH(\[Create Date]) and COUNT(\[Id])
| Month | Count of Cards |
| ----- | -------------- |
| Jan | 45 |
| Feb | 19 |
| Mar | 22 |
RUNNING\_DIFF(COUNT(\[Id])) will give us
| Month | Diff Count of Cards |
| ----- | ------------------- |
| Jan | 0 |
| Feb | -26 |
| Mar | 3 |
####
#### BINS(numerical expression)
Returns the categorical ordered intervals built by income numeric values.\
*Sample: BINS(\[Effort])*
#### ROUND(numerical expression, places)
Rounds a number to a certain number of decimal places according to standard rules.\
*827 =ROUND(826.645, 0)*\
*827 =ROUND(826.645)*\
*826.6 =ROUND(826.645, 1)*\
*826.65 =ROUND(826.645, 2)*\
*826.645 =ROUND(826.645, 3)*\
*830 =ROUND(826.645, -1)*\
*800 =ROUND(826.645, -2)*
#### ABS(numerical expression)
Returns the absolute value of a number\
*Sample: ABS(-3) will return 3*
#### MEDIAN(numerical expression)
Returns the median value in a numeric data set. *Sample: MEDIAN(\[Effort])*
#### PERCENTILE(numerical expression\\, percentile)
Returns the value at a given percentile of a dataset. The 50th percentile, that is setting PERCENTILE to 0.5 is equivalent to using MEDIAN with the same data set. *Sample: PERCENTILE(\[Effort], 0.3)*
## Date Calculations
Date functions used for aggregating data over periods of time
#### MINUTE(date expression)
Returns the date, truncating seconds of the expression.\
*Sample: MINUTE(\[Create Date])*
#### HOUR(date expression)
Returns the date truncating minutes of the expression.\
*Sample: HOUR(\[Create Date])*
#### DAY(date expression)
Returns the date of the expression (DD-MMM-YYYY).\
*Sample: DAY(\[Create Date])*
#### WEEK(date expression)
Returns the week of the date (week starts from Monday).\
*Sample: WEEK(\[Date])*
#### MONTH(date expression)
Returns the month of the date.\
*Sample: MONTH(\[Create Date])*
#### QUARTER(date expression)
Returns the quarter of the date.\
*Sample: QUARTER(\[Date])*
#### YEAR(date expression)
Returns the year of the date.\
*Sample: QUARTER(\[Create Date])*
#### AUTO(date expression)
Returns a date using the most suitable date aggregation (DAY, WEEK, MONTH and etc.) automatically based on differences between the minimum and maximum of date expressions in the data set.\
*Sample: AUTO(\[Create Date])*
#### TIMELINE(start field, end field \[, stop on current date, timeline start field, timeline end field])
Special aggregation function used for grouping data by date period, defined by start and end. Optional stop on the current date can be true or false to indicate if a timeline should be stopped at the current date. Optional timeline start and end fields are used to get min and max dates for date scale.\
*Sample: TIMELINE(\[Date], \[Valid To])*
#### TODAY() or NOW()
Returns the current date as date value.\
*Sample: TODAY()*
#### DATEDIFF(start, end, unit)
Calculates the number of days, months, etc. between two dates. Available units are year, month, week, day, hour, minute.\
*Sample: DATEDIFF(\[Date], \[Valid To], 'day')*
#### DATE(string expression)
Converts a provided date string in a known format to a date value.\
*Sample: DATE('1 Jan 2018')*
## Text Calculations
#### {text 1} + {text 2}
Concats two strings\
*Sample: \[Members] + ' is super hero'*
####
#### UPPER(text expression)
Converts text to upper case.\
*Sample: UPPER(\[Board])*
#### LOWER(text expression)
Converts text to lower case.\
*Sample: LOWER(\[Board])*
#### LEFT(text expression, N)
Returns first N symbols of text.\
*Sample: LEFT(\[Board], 3)*
#### RIGHT(text expression, N)
Returns last N symbols of text.\
*Sample: RIGHT(\[Board], 3)*
#### REPLACE(text expression, text to be replaced, replacement)
Replaces text to be replaced in an income text expression with its replacement\
*Sample: REPLACE(\[Board], 'Vizydrop', 'VZDRP')*
#### LENGTH(text expression)
Returns the length of text.\
*Sample: UPPER(\[Board])*
## **Logical operations and functions**
#### {expression 1} == {expression 2}
Returns \`TRUE\` if the two specified values are equal, and \`FALSE\` otherwise.\
*Sample:"Oleg" == "Katrin"*
####
#### {expression 1} != {expression 2}
Returns \`TRUE\` if the two specified values are not equal, and \`FALSE\` otherwise.\
*Sample: 13 != 2\*(2 + 4)*
####
#### {expression 1} > {expression 2}
Returns \`TRUE\` if the first argument is strictly greater than the second, and \`FALSE\` otherwise.\
*Sample: 3 > 2*
####
#### {expression 1} >= {expression 2}
Returns \`TRUE\` if the first argument is greater than or equal to the second, and \`FALSE\` otherwise.\
*Sample: 3 >= 2*
####
#### {expression 1} < {expression 2}
Returns \`TRUE\` if the first argument is less than the second, and \`FALSE\` otherwise.\
*Sample: 2 < 12*
####
#### {expression 1} <= {expression 2}
Returns \`TRUE\` if the first argument is less than or equal to the second, and \`FALSE\` otherwise.\
*Sample: 2 <= 2*
####
#### {logical expression 1} OR {logical expression 2}
Returns \`TRUE\` if any of the provided arguments are logically true, and \`FALSE\` if all of the provided arguments are logically false.\
*Sample: 2 <= 1 OR 1 != 0*
####
#### {logical expression 1} AND {logical expression 2}
Returns \`TRUE\` if all of the provided arguments are logically true, and \`FALSE\` if any of the provided arguments are logically false.\
*Sample: (2 <= 1 AND 1 != 0) AND "Oleg" != "Katrin"*
####
#### IF(logical expression, expression 1, expression 2)
Returns expression 1 if a logical expression is \`TRUE\`, and expression 2 if it is \`FALSE\`.\
*Sample: IF("Oleg" != \[Members], \[Effort] + 10, \[Effort] + 5)*
####
#### IFNONE(expression, default)
Replaces NULL with the specified default value.\
*Sample: IFNONE(\[Members], 'Not Assigned')*
####
#### IS\_NULL(expression)
Returns \`TRUE\` if expression is null or undefined, and \`FALSE\` otherwise.\
*Sample: IS\_NULL(\[Board])*
####
#### IS\_NOT\_NULL(expression)
Returns \`TRUE\` if expression is not null or undefined, and \`FALSE\` otherwise.\
*Sample: IS\_NOT\_NULL(\[Board])*
####
#### CONTAINS({text expression}, {text})
Returns \`TRUE\` if expression contains the defined text, and \`FALSE\` otherwise.\
*Sample: CONTAINS(\[Members] , 'Katrin')*
####
#### LIKE({text expression} , {pattern})
Returns \`TRUE\` if expression matches the defined pattern, and \`FALSE\` otherwise.\
*Sample: LIKE(\[Members], 'Kat%')*
####
#### TEXT\_AGG({text expression})
Combines all corresponding values of categorical variables for data points into a single value. *Sample: TEXT\_AGG(\[Members])*
# Sharing
Share your created visualizations with colleagues, friends, your mom, and the whole world using our platform.
# Export & Print
You can print your chart or export it into PNG, CSV, JSON via the actions menu on the view chart page.
# Annotations
Sometimes you'll need to create milestones or threshold lines. This is possible by creating annotations for dimensions. You can created dynamic or static annotations via the dimension popup. Please see the examples below.
The following math and statistical functions are available for numeric annotations:
## SUM(numerical expression)
Returns the sum of a series of numbers.
## AVG(numerical expression)
Returns the average of a series of numbers.
## MIN(numerical expression)
Returns the minimum of a series of numbers.
## MAX(numerical expression)
Returns the maximum of a series of numbers.
## MEDIAN(numerical expression)
Returns the number in the middle of a set of numbers.
## STDEV(numerical expression)
Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
# Filters
There are several ways to filter out data for a chart. The first one is very effective at filtering data.
One more way is to filter out data using the legend, or excluding via tooltip. It is a very suitable during data exploration and it is temporary filtering which means that it is set during current chart viewing session and will be reset in feature chart browsing.
# Data Reveal
When you hover over any graphical element, a tooltip popup will appear. Press the **"data" button** to reveal a list of all data related to the graphic.
# Facets
Every report can have two or more Y, two X or more X, in this case we call this report faceted. Basically, it splits a chart and draws it for every variable as a facet. For example, you can create a chart that shows dynamic of cards creation by time for all your Trello boards.
What can we say about the above chart? It is clear that [Subnautica Development board](https://trello.com/b/yxoJrFgP/subnautica-development) has more cards created during all time and [Habitica board](https://trello.com/b/EpoYEYod/habitica) is almost dead from middle of 2016.
# Equalizing
Sometimes, you need to have a few numeric values on one axis. For example, when you want to have both a count of created cards, and a [difference calculation](https://trello-guide.vizydrop.com/calculations.html) for created cards in comparison with a previous period, all in one axis. See how this can be configured below.
# Grouping Lists
Sometimes, you need to group lists together. Let's use the [Trello Public Roadmap board](https://trello.com/b/nC8QJJoZ/trello-development-roadmap) and Cumulative Flow chart as examples.
As you can see, there are Live lists created for every quarter. We need to unite them into a single group, because we're interested in total feature progress, rather than tracking separate cards.
This can be done using the `CASE WHEN` statement. So, we need to select 'List' in the Color panel, and change the calculation to the following:
`CASE WHEN CONTAINS([List], 'Live') THEN 'Live' ELSE [List]`
We then need to rename the Axis Label to 'List', since it is currently auto-named.
# Date Dimension
## Continuous vs Period Scale
We support two scales of date in Vizydrop: **continuous** and **period**. Date scale can be switched in the date dimension.
The main difference between these date scales is how they are rendered on plots, and how the value of axes in the scale are treated. Values on a period scale are treated as ordered unique categories, while the continuous scale is treated like a linear scale. See our example below on how the same data can be represented using different date scales.
## Grouping data by date
Very often, data needs to be aggregated by specific time periods, such as months or quarters. For example, when you need to see how many cards were created during a month (as shown on the chart above). We support the following grouping periods for dates: **DAY**, **WEEK**, **MONTH**, **QUARTER** and **YEAR**. There is also a *magic* **AUTO** option, which allows us to peek the most suitable date period grouping automatically. This grouping can be selected on the UI by clicking on the corresponding option in the data section of the dimension popup. It can also be provided using calculations.
# Burn Up
The main goal of a burn up chart is to show how a defined scope of work is completed to meet the desired milestone in a concrete period of time. We don't have a predefined burn up template for your boards since the platform cannot yet automatically recognize the semantics of your lists. This means that it is not clear to the application what items in the list are in your backlog, in progress, or completed.
We are going to show you how to build a burn up from scratch. We'll be using the public board of [SUBNAUTICA](https://trello.com/b/yxoJrFgP/subnautica-development) (thank you guys for the great live sample).
### 1. **Create chart from blank**
Create a new report with "History of cards" as your source, then select board and period. Check the "Cumulative" option, since we need to include records that had changes made before the selected interval.
### 2. **Timeline**
Let's drop the `Timeline` field on the X axis. This creates a historical calculation of date intervals distribution.
### 3. **Total scope**
The next thing to do is configure a total count of records line to build the scope of cards yet to be completed. We need to have a criterion, like the iteration tag of the card we need to analyze, or the name of your ''Backlog'' list with items completed before the selected date range in order to exclude them. In this example, we'll just exclude cards from the list named `Backlog`.\
Click the "+" sign on the Y axis, and then provide the total scope calculation. In our case, it looks like this: `COUNTIF_DISTINCT([Card Id],NOT(LIKE([List], '%Backlog%')) AND NOT(LIKE([List], '%Done%') and [Date] < [Timeline Start]))`. In this example, the total scope of cards to be done is formed from all cards which are not in the backlog and were not completed before the start of the selected date range.
{% hint style="info" %}
NOTE: If you're using sprint boards like [this one](https://trello.com/b/Nr3RvsY1/sprint-template), you don't need to filter out cards for the scope line. Feel free to use `COUNT_DISTINCT([Card Id])` since all cards on your board should be done.
{% endhint %}
###
### 4. **Completed**
Now we need to build a line which shows how cards are completed during the desired period. For this, we need to add a calculation to the Y axis based on a count of cards count dropped into final (done) lists after the date range's start date. For SUBNAUTICA, the calculation looks like this: `COUNTIF_DISTINCT([Card Id], LIKE([List], ‘%Done%’) AND [Date] >= [Timeline Start])`.
### 5. **Ideal line**
Let's add an **ideal line** to show when the defined scope should be completed in an ideal world. We'll use annotations for this by providing a calculation for the Total Scope dimension: `IDEAL_LINE(FIRST([Completed]), LAST([Total Scope]))`.
### 6. **Forecast line**
Sometimes, it can be quite useful to see an approximate `forecast` on when cards will actually be completed. Let's add the corresponding annotation for that: `FORECAST_LINE([Completed], LAST([Total Scope]))`.
### 7. **Total Scope**
A `total scope basic line` also can be added in the same way: `LAST([Total Scope])`.
Finally, we get the **Burn up Chart** shown below. We know it's not easy thing to learn at first, but we believe that after some practice, you will be able to build much trickier things like a pro.
