Creating a multi-dataset chart

To create a multi-dataset chart:

1. Go to the DataLens home page.

2. Click Create chart.

3. Under Dataset, select a dataset for visualization. If you do not have a dataset, create one.

4. Add another dataset. To do this, under the dataset list, click Add dataset and select the dataset to visualize.

5. When you add a dataset, a link is automatically created based on the first field name and data type match. In the dataset link configuration window that opens, click Save.

6. Repeat steps 3-4 as required.

7. Select the chart type, e.g., Line chart.

8. Drag one of the linked dimensions to the X section. The values will be displayed in the lower part of the chart on the X axis.

9. Drag measures from different datasets to the Y section. The values will be displayed as charts on the Y axis.

10. Drag a dimension or measure from the dataset to the Filters section. The field can be empty. In this case, no filters are applied.

Example of creating a multi-dataset chartExample of creating a multi-dataset chart

As an example, we will use free datasets from the marketplace as the data source:

1. Deploy the marketplace dataset named Coronavirus. Dashboard and data.

2. Similarly, deploy the marketplace dataset named Weather.

3. Use the left-hand navigation panel to switch to the Datasets section and select the Isolation Daily dataset.

   

4. At the top right, click Create chart.

5. Select Line chart as your chart type.

6. Drag the Date dimension to the X section.

7. Drag the City dimension to the Filters section. Select a value, e.g., Moscow.

8. Drag the Index measure to the Y section. The resulting chart displays changes to the self-isolation index in Moscow.

   

9. At the top left of the screen, click in front of the Dataset: Isolation Daily field.

10. Click Add dataset and select the Weather dataset.

11. In the window that opens, make sure the links are set by the Date, City, and Country fields.

    

12. In the chart dataset list, select Weather.

    

13. Drag the Average temperature measure to the Y2 section.

14. Drag the Date dimension to the Filters section. Set the condition: later than February 21, 2020 (date from which the self-isolation index is calculated).

15. At the top right, click Save. Enter the chart name and click Save.

The resulting chart visualizes data from the two datasets and allows comparing the weather and self-isolation data.

Example of creating a multi-dataset chart with SQL queriesExample of creating a multi-dataset chart with SQL queries

We will use a Moscow retail chain's demo sales ClickHouse® database as our data source. For an example of creating a connection, see this guide.

1. Build two datasets using a SQL query against the data source:

   • SQL query code for the count_client_sql dataset:

     SELECT samples.MS_Shops.ShopName, count(t2.ClientID) AS count_clients
     FROM samples.MS_Shops,
     (SELECT DISTINCT samples.MS_Shops.ShopID, samples.MS_Shops.ShopName, samples.MS_SalesFacts.ClientID
     FROM samples.MS_SalesFacts
     INNER JOIN samples.MS_Shops
     ON samples.MS_Shops.ShopID=samples.MS_SalesFacts.ShopID) AS t2
     WHERE t2.ShopName=samples.MS_Shops.ShopName
     GROUP BY ShopName
     

   • SQL query code for the count_sales_sql dataset:

     SELECT DISTINCT samples.MS_Shops.ShopName, COUNT(*) AS count_sales
     FROM samples.MS_SalesFacts
     INNER JOIN samples.MS_Shops
     ON samples.MS_Shops.ShopID=samples.MS_SalesFacts.ShopID
     GROUP BY ShopName
     

2. On the DataLens home page, click Create chart.

3. Under Dataset, select the count_client_sql dataset.

   

4. Select Line chart as your chart type.

5. Drag the ShopName dimension from the dataset to the X section. The values will be displayed in the lower part of the chart on the X axis.

6. Drag the count_clients measure from the dataset to the Y section. This will display the values along the Y axis as a chart.

   

7. Under the dataset list, click Add dataset and select count_sales_sql.

8. In the window that opens, a link will be automatically created by the ShopName field. Click Save.

   

9. Drag the count_sales measure from the dataset to the Y section. This will display the values along the Y axis as a second chart.

   

10. At the top of the screen, click Save. In the resulting window, enter the following name for the chart: Customers and sales by store - Line chart. Then click Save.

    

ClickHouse® is a registered trademark of ClickHouse, Inc.