使用限制

明细表不支持使用LOD函数。

背景介绍

目前Quick BI的分析主要以固定维度为主，比如用户想要看区域、省份下的订单数量，就需要在交叉表上的行上放置区域、省份（维度），在列上放置订数量（度量），并且设置聚合方式为求和。

这时生成的查询SQL如下：

SELECT
  ADR_T_1_.`area` AS T_AAC_2_,
  ADR_T_1_.`province` AS T_A9E_3_,
  SUM(ADR_T_1_.`order_amt`) AS T_AAD_4_
  `qbi4test`.`company_sales_record` AS ADR_T_1_
GROUP BY
  ADR_T_1_.`area`,
  ADR_T_1_.`province`
ORDER BY
  T_AAC_2_ ASC,
  T_A9E_3_ ASC

但是如果用户想在同一张表格上看到区域、省份粒度下的数据，又想看到区域的数据应该怎么办呢？或者用户想在同一张表格中看到区域、省份粒度下的数据，又想看到省份数据与对应区域数据的比值或者差值等数据呢？又或者用户想看到每个省份下哪个产品类型的销售金额最大应该怎么办？此时，我们需要用到 LOD函数 去解决以上场景。

使用场景

详细级别表达式 ，其中 详细级别 指 数据聚合粒度的层次 ，不同的级别代表着数据不同的聚合度和粒度，能够处理在一个可视化视图中包含 多个数据详细级别 的问题。

如果分析过程中需要添加一个 维度 ，其 明细程度 高于或者低于已有视图的可视化明细程度，但又不希望改变现有图形展示内容，就可采用详细级别表达式功能。

语法说明

LOD，即Level Of Detail Expressions（详细级别表达式） ，是一种强大的计算功能，它允许我们在数据分析中创建更复杂的计算和聚合。LOD表达式可以在做数据分析时控制要计算的粒度，帮助用户在指定粒度级别（FIXED级别）、较高粒度级别（INCLUDE级别）、较低粒度级别（EXCLUDE级别）对数据进行分析。基本用法如下：

LOD_FIXED

LOD_INCLUDE

LOD_ EXCLUDE

操作步骤

1. 数据集编辑页面单击 新建计算字段 ，进入配置弹窗页面。

   

2. 填写 字段名称 （①），在 字段表达式 中选择需要引用的 LOD函数 及 字段 （②）。

   

3. 新建完成后，单击 确定 按钮。使用 新增字段 创建仪表板图表时，可以看到相同区域的订单金额总额，不受产品类型影响而变化。

   

表达式说明

基本公式

三种LOD表达式的使用结构和语法分别如下：

• 使用结构

  • LOD_FIXED{<维度声明> : <聚合表达式>}

  • LOD_INCLUDE{<维度声明> : <聚合表达式>}

  • LOD_EXCLUDE{<维度声明> : <聚合表达式>}

示例：LOD_FIXED{ [订单日期]:sum([订单金额])}

• 语法说明

  • FIXED |INCLUDE|EXCLUDE ：定界关键字，制定了LOD的范围。

  • <维度声明> ：指定聚合表达式要连接到的一个或多个维度。使用逗号分隔各个维度。

  • <聚合表达式> ：聚合表达式是所执行的计算，用于定义目标维度。

过滤条件

在Quick BI中除了基本的公式外，还支持写过滤条件，具体表达式如下，以冒号隔开维度声明、聚合表达式和过滤条件。

• LOD_FIXED{维度1,维度2...:聚合表达式:过滤条件}

• LOD_INCLUDE{维度1,维度2...:聚合表达式:过滤条件}

• LOD_EXCLUDE{维度1,维度2...:聚合表达式:过滤条件}

实现原理

在实际场景中，LOD表达式生成的聚合数据通常需要与原始数据或其他聚合级别的数据合并以提供丰富的数据视图。这在SQL中通常需要使用子查询和JOIN操作来实现。接下来本节会分别介绍FIXED级别、INCLUDE级别以及EXCLUDE级别LOD表达式的实现原理。

FIXED级别

回到背景介绍中提到的问题，如果用户想在同一张表格上看到区域、省份粒度下的数据，又想看到区域的数据应该怎么办呢？

这个时候我们可以创建一个FIXED表达式：

LOD_FIXED{[area]:sum([order_amt])}

在SQL中，可以通过以下查询实现，其中我们创建一个子查询来计算每个区域下的订单数量，然后将这个子查询的结果与原始查询JOIN在一起：

-- 简化结构
-- lod_fixed{[area]:sum([order_amt])}
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,			-- area
    LOD_TM.`LOD_55512959145CF3` AS LOD_1,			-- province
    LOD_TM.`LOD_8BE7507A47AD81` AS LOD_2,			-- order_amt
    LOD_TP_0.`LOD_measure_result` AS LOD_3		-- lod_fixed{[area]:sum([order_amt])}
        SELECT		-- 主查询：area、province、sum(order_amt)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            SUM(ADR_T_1_.`order_amt`) AS LOD_8BE7507A47AD81
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`,
            ADR_T_1_.`province`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC,
            LOD_55512959145CF3 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT			-- lod子查询：area、sum(order_amt)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            sum(ADR_T_1_.`order_amt`) AS LOD_measure_result
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`
-- 标准结构
-- lod_fixed{[area]:sum([order_amt])}
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,
    LOD_TM.`LOD_55512959145CF3` AS LOD_1,
    LOD_TM.`LOD_8BE7507A47AD81` AS LOD_2,
    LOD_TP_0.`LOD_9D09E63F2E93FA` AS LOD_3
        SELECT
            SUM(ADR_T_1_.`order_amt`) AS LOD_8BE7507A47AD81,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`province`,
            ADR_T_1_.`area`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC,
            LOD_55512959145CF3 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT
            LOD_TL.`LOD_07AEF3F2F99A95` AS LOD_07AEF3F2F99A95,
            LOD_TL.`LOD_55512959145CF3` AS LOD_55512959145CF3,
            SUM(LOD_TR.`LOD_measure_result`) AS LOD_9D09E63F2E93FA
                SELECT
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`province`,
                    ADR_T_1_.`area`
            ) AS LOD_TL
            INNER JOIN (
                SELECT
                    sum(ADR_T_1_.`order_amt`) AS LOD_measure_result,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`area`
            ) AS LOD_TR ON LOD_TL.`LOD_07AEF3F2F99A95` = LOD_TR.`LOD_07AEF3F2F99A95`
        GROUP BY
            LOD_TL.`LOD_07AEF3F2F99A95`,
            LOD_TL.`LOD_55512959145CF3`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`
    AND LOD_TM.`LOD_55512959145CF3` = LOD_TP_0.`LOD_55512959145CF3`

INCLUDE级别

如果用户想在同一张表格上看到区域粒度下的数据，又想看到区域下订单量最大的省份占比该怎么实现呢？

这个时候我们可以创建一个INCLUDE表达式：

MAX(LOD_INCLUDE{[province]:SUM([order_amt])}) / SUM([order_amt])

在SQL中，可以通过以下查询实现，其中我们创建一个子查询来计算每个区域-省份下的订单数量，然后将这个子查询的结果与原始查询JOIN在一起：

-- 简化结构
-- MAX(lod_include{[province]:SUM([order_amt])}) / SUM([order_amt])
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,		-- area
    LOD_TP_0.`LOD_EC796C51A8ABAB` / LOD_TM.`temp_calculation_0` AS LOD_1  -- MAX(lod_include{[province]:SUM([order_amt])}) / SUM([order_amt])
        SELECT	-- 主查询：area、sum(order_amt)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            sum(ADR_T_1_.`order_amt`) AS temp_calculation_0
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (		
        SELECT		-- lod子查询：area、max(order_amt)
            LOD_TP_0.`LOD_07AEF3F2F99A95` AS LOD_07AEF3F2F99A95,
            MAX(LOD_TP_0.`LOD_measure_result`) AS LOD_EC796C51A8ABAB
                SELECT		-- area、province、sum(order_amt)
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    SUM(ADR_T_1_.`order_amt`) AS LOD_measure_result
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`area`
                    ADR_T_1_.`province`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`
-- 标准结构
-- MAX(lod_include{[province]:SUM([order_amt])}) / SUM([order_amt])
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,
    LOD_TP_0.`LOD_EC796C51A8ABAB` / LOD_TM.`temp_calculation_0` AS LOD_1
        SELECT
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            sum(ADR_T_1_.`order_amt`) AS temp_calculation_0
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT
            LOD_TL.`LOD_07AEF3F2F99A95` AS LOD_07AEF3F2F99A95,
            MAX(LOD_TR.`LOD_measure_result`) AS LOD_EC796C51A8ABAB
                SELECT
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`province`,
                    ADR_T_1_.`area`
            ) AS LOD_TL
            INNER JOIN (
                SELECT
                    SUM(ADR_T_1_.`order_amt`) AS LOD_measure_result,
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`province`,
                    ADR_T_1_.`area`
            ) AS LOD_TR ON LOD_TL.`LOD_07AEF3F2F99A95` = LOD_TR.`LOD_07AEF3F2F99A95`
        GROUP BY
            LOD_TL.`LOD_07AEF3F2F99A95`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`

EXCLUDE级别

对于上面「如果用户想在同一张表格上看到区域、省份粒度下的数据，又想看到区域的数据应该怎么办呢？」这个问题，FIXED级别的LOD表达式能让我们直接指定某个具体的维度「area」进行聚合来得到我们想要的一列分析数据。那我们换个思路，图表上已经拖进来了区域和省份两个维度，这个时候我们想要聚合的维度「area」已经出现了，是不是可以排除掉其他的维度「province」再聚合，这样是不是同样也能得到我们想要的数据呢，答案是肯定的，这也是EXCLUDE级别的LOD表达式的思路。

这个时候我们可以创建一个EXCLUDE表达式：

LOD_EXCLUDE{[province]:SUM([order_number])}

在SQL中，可以通过以下查询实现，其中我们创建一个子查询来计算每个区域下的订单数量，然后将这个子查询的结果与原始查询JOIN在一起：

-- 简化结构
-- lod_EXCLUDE{[province]:SUM([order_number])}
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,       -- area
    LOD_TM.`LOD_55512959145CF3` AS LOD_1,       -- province
    LOD_TM.`LOD_140423A9870F07` AS LOD_2,       -- order_number
    LOD_TP_0.`LOD_measure_result` AS LOD_3      -- lod_EXCLUDE{[province]:SUM([order_number])}
        SELECT      -- 主查询: area、province、sum(order_number)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            SUM(ADR_T_1_.`order_number`) AS LOD_140423A9870F07
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`,
            ADR_T_1_.`province`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC,
            LOD_55512959145CF3 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT      -- lod子查询：area、sum(order_number)
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95,
            SUM(ADR_T_1_.`order_number`) AS LOD_measure_result
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`area`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`
    AND LOD_TM.`LOD_55512959145CF3` = LOD_TP_0.`LOD_55512959145CF3`
-- 标准结构
-- lod_EXCLUDE{[province]:SUM([order_number])}
SELECT
    LOD_TM.`LOD_07AEF3F2F99A95` AS LOD_0,
    LOD_TM.`LOD_55512959145CF3` AS LOD_1,
    LOD_TM.`LOD_140423A9870F07` AS LOD_2,
    LOD_TP_0.`LOD_90EDFE3F5B628A` AS LOD_3
        SELECT
            SUM(ADR_T_1_.`order_number`) AS LOD_140423A9870F07,
            ADR_T_1_.`province` AS LOD_55512959145CF3,
            ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
            `qbi4test`.`company_sales_record` AS ADR_T_1_
        GROUP BY
            ADR_T_1_.`province`,
            ADR_T_1_.`area`
        ORDER BY
            LOD_07AEF3F2F99A95 ASC,
            LOD_55512959145CF3 ASC
        LIMIT
            0, 20
    ) AS LOD_TM
    INNER JOIN (
        SELECT
            LOD_TL.`LOD_07AEF3F2F99A95` AS LOD_07AEF3F2F99A95,
            LOD_TL.`LOD_55512959145CF3` AS LOD_55512959145CF3,
            SUM(LOD_TR.`LOD_measure_result`) AS LOD_90EDFE3F5B628A
                SELECT
                    ADR_T_1_.`province` AS LOD_55512959145CF3,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`province`,
                    ADR_T_1_.`area`
            ) AS LOD_TL
            INNER JOIN (
                SELECT
                    SUM(ADR_T_1_.`order_number`) AS LOD_measure_result,
                    ADR_T_1_.`area` AS LOD_07AEF3F2F99A95
                    `qbi4test`.`company_sales_record` AS ADR_T_1_
                GROUP BY
                    ADR_T_1_.`area`
            ) AS LOD_TR ON LOD_TL.`LOD_07AEF3F2F99A95` = LOD_TR.`LOD_07AEF3F2F99A95`
        GROUP BY
            LOD_TL.`LOD_07AEF3F2F99A95`,
            LOD_TL.`LOD_55512959145CF3`
    ) AS LOD_TP_0 ON LOD_TM.`LOD_07AEF3F2F99A95` = LOD_TP_0.`LOD_07AEF3F2F99A95`
    AND LOD_TM.`LOD_55512959145CF3` = LOD_TP_0.`LOD_55512959145CF3`

FIXED函数应用

FIXED详细级别表达式 使用 指定 的 维度 进行计算，而不引用其他任何维度。

应用场景一：计算每个区域的销售金额总和

场景描述

当您在分析地区分布下销售订单情况时，数据表中存在维度 区域 和 省份 ，需要用FIXED计算 区域 对应的金额总和，因为FIXED详细级别表达式 不考虑其他维度级别 ，只计算表达式中引用的维度，故可以计算出对应区域的销售金额和。

操作步骤

1. 新建计算字段

   字段表达式：LOD_FIXED{[区域]:BI_SUM([订单金额])}

   含义：按照区域求出订单金额的和。

   

2. 创建图表。

   本例中，我们创建一个交叉表。

   将上一步创建的 各地区金额和 字段拖入列区域，将 区域 、 省份 字段拖入行区域，单击 更新 后系统自动更新图表。

   

   此时，我们可以同个区域的金额和是一样的，不受省份的影响。

应用场景二：客户订单频率

场景描述

某公司销售经理想了解订购过一个订单、两个订单、三个订单（依次类推）的客户数目，通过查看客户购买 次数 的数量和分布，来分析客户的 复购黏性 。该场景下，我们可以使用详细级别表达式（LOD函数）实现按照某一度量划分另一度量的目的。

本例中，我们使用 LOD_FIXED 函数，将订单数转变为按客户数划分的维度，求出 客户订单频率 。

操作步骤

1. 新建计算字段。

   字段表达式：LOD_FIXED{[customer_name]:COUNT([order_id])}

   含义：根据用户名称求出每个用户的购买的次数。

2. 创建图表

   本例中，我们创建一个柱图。

   将上一步创建的 购买次数 字段拖入类别轴/维度轴区域，将 customer_name 字段拖入轴值/度量区域并设置去重计数，单击 更新 后系统自动更新图表。

   

此时，我们可以看到购买7次的客户数量最多，最多的一个客户购买了58次。

应用场景三：各地区利润百分比排行榜

场景描述

某公司区域销售总监想知道每个地区对总利润的占比，并一目了然地看出哪些地区的贡献度最大。该场景下，我们可以使用高级计算 -> 占比来实现，也可以使用详细级别表达式（LOD函数）来更灵活的实现。

本例中，我们使用 LOD_FIXED 函数求出 各地区利润百分比排行榜 。

操作步骤

1. 新建计算字段。

   1. 字段表达式：LOD_FIXED{:SUM([profit_amt])}

      含义：在本例中，FIXED的写法更特殊一些，该表达式并没有指定维度，因此它的含义是不根据任何粒度进行聚合，直接求出利润额的总和。

   2. 再用SUM([profit_amt])来除以之前的LOD函数，如表达式所示：SUM([profit_amt]) / SUM(LOD_FIXED{:SUM([profit_amt])})，得到的就是各个区域的利润占比。

      

2. 创建图表。

   本例中，我们创建一个排行榜。

   将上一步创建的 利润总额百分比 字段拖入指标/度量区域，将 area 字段拖入类别/维度区域，单击 更新 ，系统自动更新图表。

   

此时，我们可以看到，华南和华北的贡献度排名前二，而华东？和西南的贡献度最低，而且利润额甚至是负数，当然华东？这个数据看起来更像是脏数据。

应用场景四：年度新增用户统计

场景描述

在一个产品中，如何判断我们的产品是否在正向增长？除了日常使用的PV和UV之外，我们还可以统计用户的忠诚率，比如使用了很久的用户，依然在使用我们的产品并做出下单等贡献时，我们就可以认为我们的产品是有粘性的。为了实现这种效果，我们可以使用详细级别表达式（LOD函数）。

本例中，我们使用 LOD_FIXED 函数实现 年度新增用户统计 。

操作步骤

1. 新建计算字段。

   字段表达式：LOD_FIXED{[customer_name]:MIN(DATE_FORMAT([buy_date], '%Y'))}

   含义：求出每个用户最早的下单时间，粒度为年，粒度可以随意调整，只要使用符合您的数据库的语法的日期函数就可以。

2. 创建图表。

   本例中，我们创建一个线图。

   将上一步创建的 客户最早购买年份 字段拖入类别轴/维度区域，将 销售额 拖入轴值/度量区域，单击 更新 ，系统自动更新图表。

   

此时，我们可以看到，2013年开始使用的用户，在整个的贡献度当中依然是非常高的，看来我们的产品粘性还是很高的。

应用场景五： 基于订单明细统计各年的新客户数趋势

场景描述

按客户粒度统计单客最早购买年份，再基于其统计客户数分布。

操作步骤

1. 新建计算字段

   1. 字段一：LOD_FIXED{[客户ID]: min(BI_YEAR([订单日期]))}

      将该字段命名为 客户首购年份 ，数据类型为维度，字段类型为文本。

   2. 字段二： 客户数 =count(distinct [客户ID])

2. 创建图表

   本例中，我们创建一个 柱图 用于展示数据，将 客户首购年份 字段拖入类别轴/维度区域，将 客户数 字段拖入值轴/度量区域，单击 更新 ，系统自动更新图表。

   

此时，我们可以看到各个年份的年度新增客户数，2021年最多，此后下降明显。

应用场景六：基于订单明细统计不同购买频次的客户数分布

场景描述

按客户粒度统计单客的购买频次，再基于其统计客户数分布。

操作步骤

1. 新建计算字段

   1. 字段一：LOD_FIXED{[客户ID]: count(distinct [订单ID])}

      将该字段命名为 客户购买频次 ，数据类型为维度，字段类型为文本。

   2. 字段二： 客户数 =count(distinct [客户ID])

2. 创建图表

   本例中，我们创建一个 柱图 用于展示数据，将 客户购买频次 字段拖入类别轴/维度区域，将 客户数 字段拖入值轴/度量区域，单击 更新 ，系统自动更新图表。

   

此时，我们可以看到，购买频次为3次的客户数最多。

应用场景七：统计各年的订单金额并与重点年2023年对比

场景描述

统计固定范围数据及对比，例如统计各年的订单数据与重点年2023年的对比。

操作步骤

1. 新建计算字段

   sum([订单金额])/
   LOD_FIXED{:
   case when BI_YEAR([订单日期]) ='2023' 
   then [订单金额] else 
   0 end)} 
   -1

   字段拆分说明：

   1. 计算 2023年的订单金额 ，公式：LOD_FIXED{:sum(case when BI_YEAR([订单日期]) ='2023' then [订单金额] else 0 end)}

   2. 求出各年的订单金额并与 2023年的订单金额 对比，公式：sum([订单金额])/[2023年的订单金额]-1，字段命名为 与2023年比 。

2. 创建图表

   本例中，我们创建一个 交叉表 ，将 订单日期（year） 字段拖入行区域，将 订单金额 字段和 与23年比 字段拖入列区域，单击 更新 ，系统自动更新图表。

   

此时，我们可以看到各个订单年的订单金额以及与2023年的对比数据。

应用场景八：各年各月的盈亏天数统计分析

场景描述

基于每天的利润统计，对每天进行盈亏打标，再根据盈亏标记、订单的年份及月份，进行天数统计。

操作步骤

1. 新建计算字段

   1. 字段一：

      case when 
      LOD_FIXED{[订单日期]:sum([利润])}>0 
      then '盈利' 
      else '亏损' 
      end

      将该字段命名为 天盈亏打标 ，数据类型为维度，字段类型为文本。

      字段拆分说明：

      1. 按照订单日期计算 利润总额 ，公式：LOD_FIXED{[订单日期]:sum([利润])}

      2. 当 利润总额 大于0时，返回“盈利”，否则返回“亏损”，公式：case when [利润总额]>0 then '盈利' else '亏损' end

   2. 字段二： 天数 =count(distinct [订单日期])

   3. 字段三： 月份 =BI_MONTH([订单日期])

2. 创建图表

   我们可以用线柱图的拆分维度制作小型图，基于堆积趋势或分类趋势两种方式进行可视化呈现，能清楚地看到数据的对比和趋势。

   本例中，我们创建一个堆积柱状图，用于展示盈亏天数分布-堆积趋势；以及两个面积图，用于展示盈亏天数分布。

   1. 我们先创建一个堆积柱状图，将 月份 字段拖入类别轴/维度区域，将 天数 字段拖入值轴/度量区域，将 天盈亏打标 字段拖入颜色图例/维度区域，将 订单日期（year） 字段拖入拆分/维度。单击 更新 后系统自动更新图表。

      

   2. 我们再创建两个面积图，一个展示亏损天数，另一个展示盈利天数。

      在两个面积图中，分别将 月份 字段拖入类别轴/维度区域，将 天数 字段拖入值轴/度量区域，将 订单日期（year） 字段拖入拆分/维度。

      在过滤器中拖入 天盈亏打标 字段，并设置展示亏损天数的过滤条件精确匹配“亏损”；展示盈利天数的过滤条件精确匹配“盈利”。

      

      单击 更新 后系统自动更新图表。

      

此时，我们可以直观地看到各年各月盈亏天数的对比和趋势。

INCLUDE 函数应用

INCLUDE详细级别表达式 将指定的 维度 作为 分组 依据进行计算。INCLUDE的作用是在已有的聚合粒度基础上，再深入一层进行分析。

应用场景一：计算平均客户销售额

场景描述

当您在分析各种产品的销售情况时，需要查看 平均客户销售额， 可以通过INCLUDE先计算每个客户的 总订单销售金额 ，再通过 平均值 的聚合方式计算展示。

操作步骤

1. 新建计算字段

   字段表达式：LOD_INCLUDE{[用户id]:SUM([订单金额])}

   含义：按照用户id计算每个客户的总订单金额。

   

2. 创建图表

   本例中，我们创建一个交叉表。

   将 订单金额 、 客户订单总额 字段拖入列区域，将 产品类型 字段拖入行区域。并将 客户订单总额 的聚合方式设置为 平均值 ，单击 更新 后系统自动更新图表。

此时，我们可以看到不同产品类型对应的平均客户销售额。

应用场景二：各个销售代表的平均最大额交易数额

场景描述

某公司销售总监需要了解每个销售代表达成的最大额交易数额按地区计算的平均值，并且在地图上进行展示。该场景下，我们可以利用详细级别表达式（LOD表达式），使数据按照地区级别直观显示，我们也可以向下查看销售代表详细级别，直观地看到哪个战区的销售数据较好，哪些较差，从而对各地区的销售代表进行不同的目标规划。

本例中，我们使用 LOD_INCLUDE 函数求出 各个销售代表的平均最大额交易数额 。

操作步骤

1. 新建计算字段。

   字段表达式：AVG(LOD_INCLUDE{[sales_name]:MAX([price])})

   含义：在原有的基础上，额外增加销售代表名字为分析粒度，并求出销售额的最大值的平均值。

   

2. 创建图表

   本例中，我们创建一个色彩地图。

   1. 将上一步创建的 各地销售代表平均最大销售金额 字段拖入色彩饱和度/度量中，将 area 字段拖入地理区域轴/维度轴中。

   2. 在样式->区块里，将该字段 最大值 的区域标红。

      

      单击 更新 ，系统自动更新图表。

      

此时，我们可以看到，华东地区的销售最大额较大，西北/西南地区的则较小。

应用场景三：计算区域订单金额大于50万的区域对应的利润总额

场景描述

按区域统计订单金额，计算大于50万的区域的利润总额。

操作步骤

1. 新建计算字段

   CASE WHEN LOD_INCLUDE{[区域]:BI_SUM([订单金额])}>500000 
   then [利润金额] 
   else 0 
   end

   字段拆分说明：

   1. 按区域计算出订单金额，字段命名为 区域订单金额 ，公式：LOD_INCLUDE{[区域]:BI_SUM([订单金额])}

   2. 找出区域订单金额超过50万的区域并计算利润金额，公式：CASE WHEN 【区域订单金额】>500000 then [利润金额] else 0 end

   3. 最后求和（用SUM或者设置图表里字段的聚合方式为求和），即可求出订单金额大于50万的区域的利润总额。

2. 创建图表

   本例中，我们创建一个 指标看板 用于展示数据，您也可以再创建一个 交叉表 用于验证数据的准确性。在 指标看板 中，将 新建的字段 拖入看板指标/度量区域，字段聚合方式为求和；在 交叉表 中，将 区域 拖入行区域，将 订单金额 、 利润金额 和 新建的字段 拖入列区域，字段聚合方式为求和，单击 更新 后系统自动更新图表。

   

   此时，我们可以看到，区域订单金额大于50万的区域为东北、华东、华北、华南这四个区域，它们的利润总额为45.9万。

应用场景四：计算 各产品类型运输方式为火车的订单金额超过10万的区域的利润总额

场景描述

此场景为 应用场景三 的升级版，在此基础上筛选了运输方式为火车，并添加了产品类型作为分组，计算各产品类型运输方式为火车的订单金额超过10万的区域的利润总额。该场景涉及到多个函数的嵌套，为了便于理解，我们可以进行拆分使用。

操作步骤

1. 新建计算字段：

   SUM(
   CASE WHEN LOD_INCLUDE{[产品类型],[区域]:sum(if([运输方式]='火车',[订单金额],0))}>100000
   then [利润金额]
   else 0
   end)

   字段拆分说明：

   1. 计算各产品类型运输方式为火车的订单金额按照区域汇总，字段命名为 产品类型-区域订单金额 ，公式：LOD_INCLUDE{[产品类型],[区域]:sum(if([运输方式]='火车',[订单金额],0))}

   2. 找出订单金额超过10万的区域并求利润总额，公式：CASE WHEN [产品类型-区域订单金额]>100000 then [利润金额] else 0 end

   3. 最后用SUM求和。

2. 创建图表

   1. 我们创建两个 指标看板 用于展示数据，在 指标看板1 中，将 新建的字段 拖入看板指标/度量区域；在 指标看板2 中，将 新建的字段 拖入看板指标/度量区域，并将 产品类型 拖入看板标签/维度区域，单击 更新 后系统自动更新图表。

      

      此时，我们可以看到，产品类型运输方式为火车的订单金额超过10万的区域的利润总额为42.6万，办公用品为14.72万、家具用品为0，技术用品为27.88万。

   2. 您也可以再创建一个 交叉表 用于验证数据的准确性，将 产品类型 、 运输方式 、 区域 拖入行区域，将 订单金额 、 利润金额 和 新建的字段 拖入列区域，单击 更新 后系统自动更新图表。

      

      以办公用品为例，可以看到运输方式为火车的订单金额大于10万的区域为东北、华东、华北和华南，对应的利润金额总和为14.72万，跟指标看板的数据一致。同样的，您也可以继续验证其他产品类型的数据以及利润总额是否正确。

应用场景五：计算 各产品类型运输方式为火车的订单金额超过10万的区域的客户数

场景描述

此场景与 应用场景四 类似，不同的是，把计算利润总额改成计算客户数。

操作步骤

1. 新建计算字段：

   COUNT(DISTINCT(
   CASE WHEN LOD_INCLUDE{[产品类型],[区域]:sum(if([运输方式]='火车',[订单金额],0))}>100000
   then [用户id]
   else null
   end))

   字段拆分说明：

   1. 计算各产品类型运输方式为火车的订单金额按照区域汇总，字段命名为 产品类型-区域订单金额 ，公式：LOD_INCLUDE{[产品类型],[区域]:sum(if([运输方式]='火车',[订单金额],0))}

   2. 找出订单金额超过10万的区域并求找到对应的客户，公式：CASE WHEN [产品类型-区域订单金额]>100000 then [用户id] else null end

   3. 最后用COUNT(DISTINCT())去重计数。

2. 创建图表

   本例中，我们创建一个 指标看板 ，将上一步 新建的字段 拖入看板指标/度量区域，单击 更新 后系统自动更新图表。 此时，我们可以看到，产品类型运输方式为火车的订单金额超过10万的区域的客户数为1026。

应用场景六： 计算区域订单金额大于50万的区域数量

场景描述

该场景与 应用场景三 类似，不同的是，把计算利润总额改成计算区域数量。

操作步骤

1. 新建字段计算：

   COUNT(DISTINCT(
   CASE WHEN LOD_INCLUDE{[区域]:sum([订单金额])}>500000
   then [区域]
   else null
   end))

   字段拆分说明：

   1. 按区域计算出订单金额，字段命名为 区域订单金额 ，公式：LOD_INCLUDE{[区域]:sum([订单金额])

   2. 找出区域订单金额超过50万的区域，公式：CASE WHEN [区域订单金额]>500000 then [区域] else null end

   3. 最后用COUNT(DISTINCT())去重计数。

2. 创建图表

   本例中，我们创建一个 指标看板 用于展示数据，您也可以再创建一个 交叉表 用于验证数据的准确性。在 指标看板 中，将 新建的字段 拖入看板指标/度量区域；在 交叉表 中，将 区域 拖入行区域，将 订单金额 拖入列区域，单击 更新 后系统自动更新图表。

   

   此时，我们可以看到，区域订单金额大于50万的区域为东北、华东、华北、华南这4个区域。

应用场景七： 计算2024年各产品类型的省份平均销售额

场景描述

计算2024年各产品类型的省份平均销售额。

操作步骤

1. 新建计算字段

   AVG(
   LOD_INCLUDE{[产品类型],[省份]:
   SUM(IF(YEAR([订单日期])='2024',[订单金额],0))
   )

   字段拆分说明：

   1. 当订单日期为2024年时，计算订单金额，字段命名为 2024年订单金额 ，公式：IF(YEAR([订单日期])='2024',[订单金额],0)

   2. 按照产品类型和省份计算销售额，公式：LOD_INCLUDE{[产品类型],[省份],SUM[ 2024年订单金额 ]}

   3. 用AVG求出平均值。

2. 创建图表

   本例中，我们创建一个 指标看板 用于展示数据，您也可以再创建一个 交叉表 用于验证数据的准确性。在 指标看板 中，将 新建的字段 拖入看板指标/度量区域，将 产品类型 拖入看板标签/维度区域；在 交叉表 中，将 产品类型 拖入行区域，将 订单金额 拖入列区域，并过滤2024年的数据。单击 更新 后系统自动更新图表。

   

   此时，我们可以看到，2024年办公用品的订单金额为23.56万，省份平均销售额为23.56万/31个省份，即7600元，其他产品类型计算方式相同。

应用场景八： 计算2024年各产品类型的省份平均客户数

场景描述

该场景与 应用场景七 类似，不同的是，把平均销售额改成平均客户数。

操作步骤

1. 新建计算字段

   AVG(
   LOD_INCLUDE{[产品类型],[省份]:
   COUNT(DISTINCT(IF(YEAR([订单日期])='2024',[用户id],null)))
   )

   字段拆分说明：

   1. 当订单日期为2024年时，计算客户数，字段命名为 2024年客户数 ，公式：IF(YEAR([订单日期])='2024',[用户id],null)

   2. 按照产品类型和省份计算客户数，公式：LOD_INCLUDE{[产品类型],[省份],SUM[2024年客户数]}

   3. 用AVG求出平均值。

2. 创建图表

   本例中，我们创建一个 指标看板 用于展示数据，您也可以再创建一个 交叉表 用于验证数据的准确性。在 指标看板 中，将 新建的字段 拖入看板指标/度量区域，将 产品类型 拖入看板标签/维度区域；在 交叉表 中，将 产品类型 拖入行区域，将 各产品类型省份客户数 拖入列区域，并过滤2024年的数据。单击 更新 后系统自动更新图表。

   各产品类型省份客户数=LOD_INCLUDE{[产品类型] , [省份] : COUNT(DISTINCT([用户id]))}

   

   此时，我们可以看到，2024年办公用品的省份客户数为212，省份平均客户数为212/31个省份，即6.839，其他产品类型计算方式相同。

应用场景九：计算 省份达成目标产品数占比及对应产品的利润目标差距

场景描述

在各省份利润目标完成比较确定的情况下，进一步分析细分到产品有多少达成、没达成的具体是哪些产品。

即在省份维度下计算各产品的利润目标差距，并统计达成目标的产品数、总产品数并进一步计算占比。

本场景需要搭建两个图表并进行联动，可以通过点击左侧的产品统计数据联动查看右侧的具体产品情况。

操作步骤

1. 新建计算字段

   1. 字段一： 产品的利润目标差距

      LOD_INCLUDE{[产品] : sum([利润]-[利润目标])}。

      含义：按照产品类型计算各产品的利润目标差距。

   2. 字段二： 达成目标产品数占比

      count(distinct 
      case when [产品的利润目标差距]>0 
      then [产品] 
      else null end)
      /count(distinct [产品])

      字段拆分说明：

      1. 当产品的利润目标差距大于0时，去重计算产品数量，求出 达成目标产品数， 公式：count(distinct case when [产品的利润目标差距]>0 then [产品] else null end)

      2. 去重计算 所有产品数 ，公式：count(distinct [产品])

      3. 将上面两个字段相除，即[ 达成目标产品数 ]/[ 所有产品数 ]，求出 达成目标产品数占比 。

2. 创建图表

   本例中，我们需要创建两个图表，并进行联动。

   1. 我们先创建一个条形图，用来展示 产品的利润目标差距 。将 产品 字段拖入类别轴/维度域，将 产品的利润目标差距 字段拖入值轴/度量区域。单击 更新 后系统自动更新图表。

      

   2. 我们再创建一个条形图，用来展示 达成目标产品数占比 。将 省份 字段拖入类别轴/维度域，将 达成目标产品数占比 字段拖入值轴/度量区域。单击 更新 后系统自动更新图表。

      

   3. 设置两个条形图的联动。

      说明

      本例中，两个条形图使用的数据集为同个数据集，若仪表板开启了自动联动，则图表之间将自动关联，无需手动配置；若未开启自动联动，您可以配置手动联动。

      具体操作请参见 联动 。

      联动效果展示。

此时，我们可以看到省份利润目标的完成情况，并查看各个省份对应的达成和没达成目标的具体产品。

应用场景十： 对各地区的门店进行经营情况的评估对比

场景描述

根据门店销售和毛利的明细数据，在各地区经营的大区及小区下，计算总的销售额和毛利，同时计算对应区域内单个店铺的平均销售额和毛利，并且可以通过筛选维度，查看大区及小区，或大区级别的门店表现，对各地区的门店进行经营情况的评估对比。

操作步骤

1. 新建计算字段

   1. 字段一： 店铺毛利 =LOD_INCLUDE{[店名]:sum([毛利])}

   2. 字段二： 店铺销售额 =LOD_INCLUDE{[店名]:sum([销售额])}

2. 创建图表

   本例中，我们创建一个 交叉表 ，并进行 条件格式 配置。

   1. 在交叉表中，将 所属大区、所属小区 拖入行区域，在列区域中，拖入两个 店铺毛利 字段，一个设置为平均值，另一个设置为求和；拖入两个 店铺销售额 字段，一个设置为平均值，另一个设置为求和。

      

   2. 将 店铺毛利（平均值） 、 店铺销售额（平均值） 的字段内容显示为 店均 ，将 店铺毛利（求和）、店铺销售额（求和） 的字段内容显示为 总额 。

   3. 在样式->单元格->指标展示分组里，设置 指标分组 。

      

   4. 为了展示更美观，您可以配置 条件格式 ，本例中四个列字段的条件格式配置如下：

   5. 单击 更新 后系统自动更新图表。

此时，我们可以直观地看到各区域对应的销售额、毛利的总额及店均数据。

应用场景十一：基于地区的省份表现对各地区进行评估

场景描述

在各个地区下，按照省份汇总计算销售额，计算其中销售额大于阈值（基于筛选条件输入）的省份数量，以及这些省份对应的客户数和平均每个省的客户数量，并可以通过输入不同的阈值查看数据结果。

操作步骤

1. 新建计算字段

   1. 字段一： 满足条件的省份数

      count(distinct 
      case when LOD_INCLUDE{[省份]:sum([订单金额])} > $val{ord_amt_level} 
      then [省份] 
      else null
      end)

      字段拆分说明：

      1. 根据省份计算订单金额，公式：LOD_INCLUDE{[省份]:sum([订单金额])，字段命名为 省份订单金额 ；

      2. 当 省份订单金额 大于值占位符 ord_amt_leve l时，返回省份，否则为空，公式：case when [ 省份订单金额 ]> $val{ord_amt_level} then [省份] else null end，字段命名为 满足条件的省份 ；

      3. 对满足条件的省份进行去重计数，公式：count(distinct [ 满足条件的省份 ])

   2. 字段二： 省份下总客户数

      count(distinct 
      case when lod_include{[省份]:sum([订单金额])} > $val{ord_amt_level} 
      then [客户ID] 
      else null 
      end)

      字段拆分说明：

      1. 根据省份计算订单金额，公式：LOD_INCLUDE{[省份]:sum([订单金额])，字段命名为 省份订单金额 ；

      2. 当 省份订单金额 大于值占位符 ord_amt_level 时，返回客户ID，否则为空，公式：case when [ 省份订单金额 ]> $val{ord_amt_level} then [客户ID] else null end，字段命名为 省份下的客户 ；

      3. 对 省份下的客户 进行去重计数，公式：count(distinct [ 省份下的客户 ])

   3. 字段三： 省份平均客户数= [ 省份下总客户数 ] / [ 满足条件的省份数 ]，即上述的字段二除以字段一。

2. 创建图表

   1. 创建一个 交叉表 ，将 地区 拖入行区域，将 省份数 、 省份客户数 、 省份平均客户数 拖入列区域。

   2. 插入图表内查询条件，设置查询条件为省份销售金额，关联值占位符 ord_amt_level 并设定默认值为100000。

   3. 单击 更新 ，系统自动更新图表。

      

此时，您可以通过输入不同的省份销售金额，筛选查看数据变化。

EXCLUDE函数应用

EXCLUDE详细级别表达式 将 去除 表达式中指定的 维度 后进行计算。

应用场景一：计算区域下各省份销售额占比

场景描述

当您在分析区域下 各省份 的 销售额数据情况 时，同时还需要查看该 区域 的 总销售数据 、以及省份与其的 销售额占比 时，可以通过EXCLUDE函数先计算出 除去当前省份 后 该地区的销售额 ，再通过聚合方式 求和 计算出该 区域 的 总额 。

操作步骤

1. 新建计算字段

   字段表达式：LOD_EXCLUDE{[省份]:SUM([订单金额])}

   含义：计算除去当前省份后该地区的销售额。

   

2. 创建图表

   本例中，我们创建一个交叉表。将 订单金额 、 区域总销售额 字段拖入列区域，将 区域 、 省份 字段拖入行区域，单击 更新 后系统自动更新图表。

   

此时，我们既可以看到各省份的订单金额，也可以看到对应区域的总销售额。

应用场景二：各个销售区域和战区平均值之间的差额

场景描述

某销售公司目前根据全国的行政区块下辖了7个大战区，每个战区下面又根据省份设立了若干个销售区域，现在到了年终盘点的时候，我们需要快速知道各个省份的限售区域在今年的平均销售利润额和战区整体平均之间的差距，并看出哪些是优秀战区，哪些仍需要提升。对于这种场景，我们可以利用详细级别表达式（LOD表达式）和条件格式快速完成这样的一个报表。

本例中，我们使用 LOD_EXCLUDE 函数求出 各个销售区域和战区平均值之间的差额 。

操作步骤

1. 新建计算字段。

   1. 字段表达式：AVG(LOD_EXCLUDE{[province]:AVG([price])})

      含义：在原有的基础上，去掉销售战区作为聚合粒度，并求出其他聚合粒度的销售额的平均值。这个表达式中，我们将会求出各战区（华东等）的销售额的平均值。

   2. 再用AVG([price])来减去之前的LOD函数，如表达式所示：AVG([price]) - AVG(LOD_EXCLUDE{[province]:AVG([price])})，得到的就是各个销售区域和战区平均值之间的差额。

      

2. 创建图表

   本例中，我们创建一个交叉表。

   1. 将上一步创建 省份平均差额 字段拖入列，将area和province字段拖入行。

   2. 在样式->条件格式里，对该字段设置条件格式：大于0的为红色，小于0的为绿色。

      

   3. 单击 更新 ，系统自定更新图表。

      

此时，我们可以快速看到，在华东战区，上海、安徽、江苏和福建的销售额是正向的，其中上海的表现最出色，而山东、江西和浙江的营业额未达到平均，还需努力。

过滤条件规则说明

LOD_FIXED函数 + 外部的过滤条件

• 计算结果不受过滤条件影响的场景

  LOD_FIXED_1字段表达式： LOD_FIXED{[区域] : SUM([订单金额])}

  过滤条件：运输方式 = “大卡”

  计算结果说明：在下图中可以看到，以东北区域为例，东北区域的订单金额合计是52.74万，外部无过滤条件或外部过滤条件为运输方式是“大卡”时，东北区域的订单金额始终是52.74万，不受过滤条件的影响。

  结论：过滤条件与LOD函数的聚合维度不同，最终的计算结果不受过滤条件影响 。

  

• 计算结果受到过滤条件影响的场景

  LOD_FIXED_2字段表达式： LOD_FIXED{[区域] , [产品类型] , [运输方式] : SUM([订单金额])}

  过滤条件：运输方式 = “大卡”

  计算结果说明：在下图中可以看到，以东北区域为例，东北区域-办公用品的订单金额合计是15.08万，是大卡（3.554万）、火车（10.31万）、空运（1.211万）三种运输方式的金额合计。当外部过滤条件为运输方式是“大卡”时，东北区域-办公用品的订单金额是3.554万，该金额为东北区域-办公用品-大卡的订单金额。

  结论：过滤条件与LOD函数的聚合维度相同，因二次聚合，最终的计算结果会受到过滤条件影响。

  

LOD函数 + 内部的过滤条件

结论：当内部过滤条件与LOD的聚合粒度一致时，相关数据会被同步过滤；否则将只对LOD字段进行过滤 。

• LOD_FIXED函数

  • LOD_FIXED_3字段表达式： LOD_FIXED{[区域] , [产品类型] , [运输方式] : SUM([订单金额]) : [订单等级] ='中级' }

    计算结果：在下图中可以看到，以东北区域为例，东北区域-办公用品-大卡的订单金额为3.507万，该金额是过滤了订单等级为中级后的数据，即为东北区域-办公用品-大卡-中级的订单金额。

    

  • LOD_FIXED_4字段表达式： LOD_FIXED{[区域] , [产品类型] , [运输方式] : SUM([订单金额]) : [运输方式] ='大卡' }

    计算结果：在下图中可以看到，以东北区域为例，东北区域-办公用品-大卡的订单金额为3.554万，即 内部过滤条件与LOD的聚合粒度一致 时，LOD_FIXED函数过滤了运输方式为大卡的数据。

    

• LOD_INCLUDE函数

  LOD_EXCLUDE和LOD_INCLUDE也有同样的逻辑，此处以LOD_INCLUDE举例。

  

  • LOD_INCLUDE_1字段表达式： LOD_INCLUDE{ : SUM([订单金额]) : [订单等级] ='中级' }

    计算结果：在下图中可以看到，以东北区域为例，东北区域-办公用品-大卡的订单金额为3.507万，该金额是过滤了订单等级为中级后的数据，即为东北区域-办公用品-大卡-中级的订单金额。

  • LOD_INCLUDE_2字段表达式： LOD_INCLUDE{ : SUM([订单金额]) : [运输方式] ='大卡' }

    在下图中可以看到，以东北区域为例，东北区域-办公用品-大卡的订单金额为3.554万，即 内部过滤条件与LOD的聚合粒度一致 时， LOD_INCLUDE 函数过滤了运输方式为大卡的数据。