FOR EACH

The FOR EACH clause allows you to display all attribute values in your visualization, even if they are missing.

If you want to display how many visits are planned for each particular month, the visualization normally shows you all months with at least one visit planned.

Table showing sum of visits by month from January to July 2023. Only months with available visit data are displayed. Months with no data such as April and June are excluded from the visualization.

If you use the FOR EACH for month of the year, the visualization will show you also the months that have no visits planned.

Table with a column for months from January to July 2023 and a column showing sum of visits. Some months, such as April and June, have no data and are displayed with a dash, indicating missing values.

When creating metrics with FOR EACH, remember that:

  • The visualizations in Analytical Designer must be sliced with the same attributes that are used in the FOR EACH clauses to display the missing values. Otherwise, FOR EACH is ignored.

  • If the visualization contains multiple metrics with FOR EACH applied, the visualization displays all attribute values.

  • When FOR EACH is applied, the visualization displays all attribute values between the first and the last metric values available.

    Two separate tables side by side showing order amounts for two customers by date. Each table includes only the dates relevant to the individual customer.

    If the visualization contains multiple metrics, it displays attribute values from all metrics.

    Combined table displaying order amounts for two customers by date. The full timeline is retained, and missing values are shown as dashes, providing a comprehensive view across both customers.

Day versus Day of Week

When With date attributes, the FOR EACH clause displays the following:

  • For month and similar date attributes, the FOR EACH clause displays attribute values from the first to the last metric values available, including the missing values in between.

    Bar chart showing monthly values for June to December 2023. August is missing, but the timeline spans from the first to the last available value, showing all intermediate months even when data is missing.

  • For month of year and similar date attributes, the FOR EACH clause display always all attribute values.

    Bar chart displaying values for each month of the year from January to December. All months are shown regardless of whether any data exists for that month.

FOR EACH versus Show All Values

The FOR EACH clause and the Show all values option in Analytical Designer show similar results.

  • FOR EACH displays attribute values between the first and the last metric values of each metric, including the missing values in between.

    If any attribute value falls outside of such intervals for all metrics, it is not displayed.

    Table showing customer order amounts for each month. The table includes only the months between the first and last metric values, omitting months that fall completely outside that range.

  • Show all values displays all attribute values regardless of the metric values available.

    Table showing customer order amounts for each month of the year. All twelve months are shown even if no values are recorded, with missing data represented by dashes.

Syntax 

SELECT ... BY ... FOR EACH (attribute/label), (attribute/label), ...

Examples

Display the sum for all sold items every month. The FOR EACH clause displays also items and months with no sale.

SELECT SUM(fact/sales) FOR EACH (attribute/saledate.month), (attribute/item)

Display the moving average. The FOR EACH clause displays also the days without any change.

SELECT RUNAVG({metric/value}) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW FOR EACH(date.day)

Display the moving average. The FOR EACH clause displays also the days without any change and shows missing values as zeros.

SELECT RUNAVG( IFNULL({metric/value},0)) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW FOR EACH(date.day)

Display the bottom 10 products with the lowest number of orders. The FOR EACH clause counts also the products with no orders.

SELECT COUNT({label/order_id}) FOR EACH {label/product_name} WHERE BOTTOM(10) OF (COUNT({label/order_id}) )

Data Source Support for Dates

Some of the data sources may not fully support FOR EACH with date attributes:

  • Dremio, Drill, Redshift, and Synapse do not support FOR EACH for date attributes.

  • SQL Server 2022 (16.x), Azure SQL Database and Azure SQL Managed Instance support date attributes only from the compatibility level 160.

Other data sources fully support FOR EACH for all attributes.

Functions For Referring to Past Time Periods
FOR Next