> ## Knowledge Base Index
> Fetch the complete knowledge base index at: https://learn.fieldproapp.com/sitemap.xml
> Use this file to discover available pages before exploring further.
> Pure-Markdown content can be obtained by appending a '.md' suffix to the content URLs listed in the sitemap (without the trailing slash).

# How to Create a Multiple Line Chart

In this guide, we'll walk you through the steps to create a Multiple Line Chart. This type of chart is perfect for visualizing and comparing multiple data trends over time. Let's get started!

* [1. Access the Dashboard](#1-access-the-dashboard)
* [2. Choose the Chart Type](#2-choose-the-chart-type)
* [3. Add a Chart Tag](#3-add-a-chart-tag)
* [4. Add a Chart Title](#4-add-a-chart-title)
* [5. Add a Chart Description](#5-add-a-chart-description)
* [6. Configure Chart Options](#6-configure-chart-options)
* [7. Choose Type of X Axis](#7-choose-type-of-x-axis)
* [8. Row Query](#8-row-query)
* [9. Sub Queries](#9-sub-queries)
* [10. Total Scorecard and Total Legend](#10-total-scorecard-and-total-legend)
* [11. Main Query](#11-main-query)
* [12. Additional Information](#12-additional-information)

# 1. Access the Dashboard

Begin by accessing your dashboard. If you haven't set up a dashboard yet, you'll need to create one. Once inside your dashboard, click on the "Add Chart" button or the plus (+) icon, depending on your current view.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-03-at-45300_1ib0zqo.png)


# 2. Choose the Chart Type

A dialog box will appear. From the dropdown list, select "Multiple Line Chart".

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-10-at-44010_1tvgztx.png)

# 3. Add a Chart Tag

Input a chart tag that relates to the chart you're crafting. For this example, use "multiple_amount".

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-25159_qquq9v.png)

# 4. Add a Chart Title

Designate a title for your chart. For this guide, we'll use "Amount Due vs Amount Deducted".

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-25210_sghp9c.png)

# 5. Add a Chart Description

Provide a description for your chart. An example could be "Amount due vs amount deducted from a sales visit".


![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-25219_jk7s53.png)

# 6. Configure Chart Options

Toggle on the option "Force the Display of the Chart in Full Width" if you want the chart to span the entire width of the screen.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-24759_13q7qp6.png)


# 7. Choose Type of X Axis

Select the type of X axis that fits your dataset. This could be "Text" for categorical data or "Time" for time series data.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-10-at-44050_1uzadb6.png)

# 8. Row Query

The row query is used to define the labels for the multiple lines in the chart. Here's an example of a row query:
This query creates two labels: "Amount Due" and "Amount Deducted". The `UNION ALL` command combines these two labels into a single result set.

|| used to define labels for the main query

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-10-at-44058_1q1ukva.png)


# 9. Sub Queries

Sub queries are essential components of a Multiple Line Chart. They define the data for each line you wish to visualize on the chart. Each sub query should return the same x values (typically representing time or categories), but different y values based on the label. Here's a step-by-step breakdown:

1. **Click on "Add Option"**: This will allow you to create a new sub query.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-10233_5tmmch.png)

2. **Dialog Pop-Up**: A dialog will appear, prompting you to define the sub query.

3. **Add a Label**: For the first sub query, you might use a label like "Amount Due". This label will represent one of the lines on your chart.

4. **Define the Query**: Your query should fetch the x values and the y values for the "Amount Due". Ensure that the x values here match the x values of other sub queries to ensure consistency across lines.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-10254_1l3iuhm.png)


5. **Add Another Sub Query**: Click on "Add New" to define the next sub query.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-17-at-34743_em87dt.png)

6. **Repeat the Process**: For the second sub query, you might use a label like "Amount Deducted". Again, ensure that the x values match those of the first sub query. The y values, however, will represent the "Amount Deducted".

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-10260_7y6kmm.png)

By structuring your sub queries in this manner, the Multiple Line Chart will display each line (like "Amount Due" and "Amount Deducted") distinctly, allowing for easy comparison of trends over the same x values.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-10285_kk42do.png)

|| In cases where the type of X axis is "Text", and you're working with dates, you might need to convert the date from a timestamp to a text format. This can be achieved using functions like `CAST` or `to_char` to ensure it aligns with the "Text" format of the X axis this is applicable using the example above.


# 10. Total Scorecard and Total Legend

In this step, you have the option to adjust the Total Scorecard and Scorecard Legend settings. These options are particularly useful when your dashboard type is set to "Mobile Only". 

- **Total Scorecard**: This displays a summarized value, providing a quick overview or total of the data being visualized in the chart.
  
- **Scorecard Legend**: This displays a legend, helping users understand the data points or categories being represented in the chart.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-10-at-44108_28xq37.png)

# 11. Main Query

The main query fetches the data for your chart. Here's a breakdown of how to structure it:

- **WITH filtered_data AS**: This section filters the data based on certain conditions.
- **aggregated_data AS**: This section aggregates the data, summing up the amounts for each label.
- **SELECT**: This final section selects the data to be displayed in the chart.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-10-at-44243_11r1gtd.png)

The use of `label IN ($row_query_result$)` ensures that only the labels defined in the row query are included in the final chart.

![](https://storage.crisp.chat/users/helpdesk/website/eccae9ddfa738000/screenshot-2023-08-21-at-10285_kk42do.png)

|| When structuring your query, ensure that the final columns are named "x" and "y". 
|| The line "WHERE label IN ($row_query_result$)" checks that the labels match those defined in the row query.
|| add your filters accordingly