Articles on: Dashboards & Jobs

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


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.




2. Choose the Chart Type


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



3. Add a Chart Tag


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



4. Add a Chart Title


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



5. Add a Chart Description


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




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.




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.



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




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.



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


  1. 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.


  1. 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.




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



  1. 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".



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.



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.



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.



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



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


Updated on: 28/08/2023

Was this article helpful?

Share your feedback

Cancel

Thank you!