Creating a multi-dataset chart
Note
In multi-dataset charts, only linked dimensions (available in all datasets) can be used.
To create a multi-dataset chart:
- On the Yandex DataLens homepage, click Create chart.
- Under Dataset, select a dataset for visualization. If you don't have a dataset, create one.
- Add another dataset. To do this, under the dataset list, click Add dataset and select the dataset to visualize.
- When you add a DataLens dataset, a link is automatically created based on the first field name and data type match. In the resulting dataset link configuration window, click Save.
- Repeat steps 3-4 as required.
- Select your chart type, such as Line chart.
- Drag a dimension from the dataset to the X section. The values are displayed in the lower part of the chart on the X-axis.
- Drag measures from different datasets to the Y section. The values are displayed as charts on the Y-axis.
- Drag a dimension or measure from the dataset to the Filters section. The field can be empty. In this case, no filters are applied.
We'll use a demo ClickHouse database with sales data from a Moscow store chain as our data source. For an example of creating a connection, see the use case.
1. Build two datasets using a SQL query against the data source:
- SQL query code for the
count_client_sqldataset:
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_sqldataset:
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 Yandex DataLens homepage, 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 your dataset to the X section.This will display the values at the bottom of the chart along the X axis.
6. Drag the count_clients indicator from your 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 resulting window, a link is automatically created based on the ShopName field. Click Save.
9. Drag the count_sales indicator from your 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.
