Tableau<15大LOD表达式进行案例挑战

0. 分析数据集

超市销售订单明细表: 包含有关产品、销售和利润的信息，您可以使用这些信息来确定该虚构公司中需要改进的关键领域。

• Row ID
• Order ID
• Order Date
• Ship Date
• Ship Mode
• Customer ID
• Customer Name
• Segment
• Country/Region
• City
• State/Province
• Postal Code
• Region
• Product ID
• Category
• Sub-Category
• Product Name
• Sales
• Quantity
• Discount
• Profit

1. 客户订单频率

找出每个客户订购的订单数相对简单，但是如果我们想了解订购过一个订单、两个订单、三个订单（依次类推）的客户数目，该怎么办呢？
要生成相应视图，我们必须按订购的订单数划分客户数。这是一个简单的问题，但是如果没有详细级别表达式(Tableau Lod)，按照某一度量划分另一度量将非常困难。

TABLEAU LOD范例

每个顾客的购买次数：{ FIXED [Customer ID]:COUNTD([Order ID])}

BI LOD

 {FIXED `customer id` : distinct(`order id`)}

2. 阵列分析

合作时间越长的客户对销售额的贡献越大吗？
下面的视图按照客户首次购买的年份将客户分组，以便对比各个阵列的年度销售贡献额。
每个客户的最早订单日期将体现出首次购买日期。不过，由于视图中的数据为订单明细，没有客户信息，我们需要新增一个客户【首次下单时间】字段

TABLEAU LOD范例

每个顾客的首次购买日期：{ FIXED [Customer ID]:MIN([Order Date])}

BI LOD

{FIXED `customer id` : min(`order date`)}

• 拖拽【order date】到行维，并设置日期粒度为年
• 拖拽【首次下单时间】到列维，并设置原始字段类型为日期，日期格式化为年
• 拖拽【sales】到指标，并设置汇总函数为sum
• 图表类型选择为柱线图, 展示样式为堆叠柱状

BI 计算字段

增加计算字段【首次下单时间】, 因为我们要按客户汇总订单数量，

1. 首先打开【汇总字段】开关
2. 拖拽【customer id】字段到分组字段栏
3. 拖拽【order date】字段到组内排序字段栏，并设置升序
4. 在【字段定义】中输入first_value(`order date`), 取组内第一个下单时间做为字段值

图表设计配置如下：

• 拖拽【order date】到行维，并设置日期粒度为年
• 拖拽【首次下单时间】到列维，并设置原始字段类型为日期，日期格式化为年
• 拖拽【sales】到指标，并设置汇总函数为sum
• 图表类型选择为柱线图, 展示样式为堆叠柱状

3. 每日利润 KPI

我们可以查看利润随时间的变化趋势，但如果我们想按照每个工作日的总利润来衡量成功，该怎么办？
我们可能想要了解每个月或每年的盈利天数，尤其是在我们想了解季节影响时。
下面的视图显示了在以交易级别记录基础数据的情况下，我们如何利用详细级别表达式轻松根据聚合数据创建分级（例如每日利润）。

Tableau LOD

在给每天返回标签时，我们需要用到每天的盈利额，这里就需要fixed 返回每一天的盈利额。

Profit per Day = { FIXED [Order Date] : SUM([Profit]) }

根据每天的盈利额，按照标准，给每天返回一个盈利标签：Daily Profit KPI

IF [Profit per Day] > 2000 THEN “Highly Profitable”
ELSEIF [Profit per Day] <=0 THEN “Unprofitable”
ELSE “Profitable” END

BI LOD

在给每天返回标签时，我们需要用到每天的盈利额，这里就需要fixed 返回每一天的盈利额。

Profit per Day = {FIXED `order date` : sum(`profit`)}

根据每天的盈利额，按照标准，给每天返回一个盈利标签：Daily Profit KPI

CASE
    WHEN #{Profit per Day} > 2000 THEN "Highly Profitable"
    WHEN #{Profit per Day} > 0 THEN "Profitable"
    ELSE "Unprofitable"
END

BI 计算字段

增加计算字段【每日利润】字段，配置如下比较简单，不在赘述

图表配置如下：

• 行维：【order date】设置日期粒度为月
• 列维：【order date】设置日期粒度为年，【每日利润】并设置维度分组
• 指标：【order date】汇总函数为count
• 图表类型选择为柱线图，面积折线，
• 样式配置：布局->拆分图表，取消图列显示

4. 总额百分比

每个国家/地区的收入对全球销售额有何贡献？
如果我们按照贡献百分比着色，即可看出美国对全球销售收入的贡献最大。

Tableau LOD

SUM([Sales]) / SUM({SUM(Sales)})

BI LOD

EXCLUDE 计算

sum(`sales`) / sum({EXCLUDE `country` : `sales`})

FIXED 计算

1. 增加计算字段总销售

{FIXED  : sum(`Sales`)}

2. 增加汇总表达式计算占比, 由于计算字段在每行中都有总销售数值，在汇总计算的时候需要用min/max/avg取值

sum(`Sales`) / max(`总销售`)

BI 快速计算

图表配置如下：

• 行维：国家
• 指标：[sales], 第二个指标也是[Sales]，值类型设置为【列占比】

5. 新客户争取率

各个市场总客户争取率的每日趋势如何？
了解这一数据趋势后，可帮助我们了解地区营销和销售组织在发展新业务方面的表现。线越陡，则争取率趋势越好。如果线逐渐变平，则必须采取一些措施来增加潜在客户流。 详细级别表达式可确保回头客不会误计入新客户，这是因为虽然数据按照市场和天数直观显示，但是必须在客户层面上进行评估。

Tableau

给每个客户一个标签，标记在当前日期下的状态

首次购买日期：

{FIXED [Customer ID]:MIN([Order Date])}

根据购买日期是否等于首次购买日期判断新老客户：

IF [Order Date] = [首次购买日期] THEN “新客户” ELSE “老客户” END

通过快速表计算，计算汇总累计客户数。

BI LOD

计算字段中新增，首次下单时间、是否为新客户 首次下单时间(1st order date)

{FIXED `customer id` : min(`order date`)}

是否为新客户(New or Existing)

IF(`order date` = #{1st order date }, 'New', 'Existing')

BI 计算字段

增加计算字段【是否为新客】字段，该字段使用了前面定义的字段字段【首次下单时间】

CASE
    WHEN #{首次下单时间} = `order date` THEN '新客'
    ELSE '老客'
END

图表配置如下：

• 行维：【order date】
• 列维：【region】
• 过滤：【是否新客】选择新客
• 指标：distinct【customer id】，值类型配置为累加
• 图表类型：柱线图，样式配置，线条样式-> 空值处理->connect连接

6. 对比销售额分析

找出与平均值之间的差异相对简单，但是如果您想找出与选定类别的差异，该怎么办？

Tableau

1. 计算字段选中销售额

IF [Sub-Category] = [Parameters].[Sub-Category] THEN Sales ELSE 0 END

BI LOD

1. 计算字段选中销售额

IF(`sub-category` = '${subcate!"Art"}', `sales`, 0)

增加一列Selected Sales，当该列类别与选中类型一致时值设置为sales，否则为0，特殊的地方这个计算字段使用了环境变量，也就是在变量改变的时候 每次这一列的值也会跟着改变

2. 汇总表达式计算选中类别销售额 Sales of Selected Category

当我们在行维度上配置了类别时，如果直接sum(Selected Sales), 这时候只有选中的类别有值，所以为了让每个类别都有值，需要用EXCLUDE排除类别，让所有类别都有选中类别销售额

{EXCLUDE `sub-category` : sum(`Selected Sales`)}

{FIXED : SUM(IF(`sub-category` = '${subcate!"Art"}', `sales`, 0))}

3. 汇总表达式 Difference From Selected, 销售额与选中销售额的差异

sum(`sales`) - ${Sales of Selected Category}

4. 实现效果如下

5. 改变变量值

BI 计算字段

增加计算字段【指定类别销售额】，开启汇总，但是不选分组字段，即全体汇总，汇总定义中使用了条件汇总，只有在【sub-category】等于环境变量${subcate}传入值时才计入汇总

sum(CASE
    WHEN `sub-category` = '${subcate}' THEN `Sales`
    ELSE 0
END)

增加数据集表达式, 因为新增字段【指定类别差额】，所有会全体汇总字段，所有数据值都相同，所以选择max或者min维持聚合后原值输出即可

sum(`sales`) - max(`指定类别销售额`)

新建看板如下：
左图为按类别排序的柱线图，行维【sub-category】不排序，指标选择【sales】求和升序 图表选择柱线图-主轴选柱图，样式微调->轴样式->值轴选择水平轴

右图为按类别排序的柱线图，行维【sub-category】不排序，指标选择【指定类别差额】 图表选择柱线图-主轴选柱图，样式微调->轴样式->值轴选择水平轴

看板中左图增加联动，行维联动到环境变量subcate，

7. 各个销售代表的平均最大额交易数额

场景描述： 每个销售代表达成的最大交易额是多少？然后想一想，对于各个销售代表达成的最大额交易数额，按国家/地区计算的平均值是多少？

利用详细级别表达式，即使数据按照国家/地区级别直观显示，我们也可以向下查看销售代表详细级别。在下面的视图中，销售代表的平均最大额交易数额在颜色为蓝色的国家/地区较高，而在颜色为橙色的国家/地区较低。我们可以使用这些信息指导对从国家/地区到销售代表的深入分析。

可视化关键： 不同地区的“最大订单金额”的平均值

Tableau LOD

计算各销售代表的最大订单金额：{INCLUDE [Sales Rep]:MAX([Sales])}

BI LOD

直接用图形化配置

或者用汇总LOD INCLUDE

avg({INCLUDE `sales rep` : max(`sales`)})

8. 实际对比目标

场景描述： 在本可视化视图中，我们描述了每个州连锁咖啡厅的实际利润与目标利润之间的差异。在顶部视图中，我们可以清楚地看出哪些州超额完成目标，哪些州未完成目标。但是采用这种聚合方式，我们会漏掉重要的细微差别。某些州超出目标是因为在该州销售的每项产品都超出目标，另外一些州超出目标是因为单项产品超额完成其目标，足以弥补未完成目标的所有其他产品。我们可以使用详细级别表达式，确定某州内销售的产品中超出目标产品所占百分比。

可视化关键： 每个州，销售超过目标计划的商品占总数占比

LOD分成几步完成：

1. 行级别计算利润利润差异
   自定义字段 Difference Between Actual and Target Profit

[Profit]- [Target Profit]

2. 在商品级别，判断利润和任务的差异
   自定义字段：Difference Between Actual and Target by Product

{ INCLUDE [Product] : SUM( [Difference Between Actual and Target Profit] ) }
// 每个商品都分为很多天的销售，计算每个商品的利润差异汇总

3. 差异大于0，标记1，否则为0，这样求和即 计数

自定义字段： Number of Products Above Target

IIF([Difference Between Actual and Target by Product] > 0, 1, 0)

4. 计算完成任务的商品数量，上面返回了1/0，求和即可

自定义字段：Percentage of Products Above Target

SUM([Number of Products Above Target]) / COUNTD([Product])

BI LOD

1. 行级别计算利润利润差异
   自定义字段 Difference Between Actual and Target Profit

`Profit` - `Target_profit`

2. 新建LOD INCLUDE 汇总字段，Product级别汇总利润差异， 差异大于0，标记1，否则为0，这样求和即计数

自定义字段： Number of Products Above Target

sum({INCLUDE `Product` :
    IF(sum(`Difference Between Actual and Target Profit`) > 0, 1, 0)
})

3. 计算完成任务的商品数量，上面返回了1/0，求和即可

自定义字段：Percentage of Products Above Target

${Number of Products Above Target} / distinct(`Product`)

9. 周期最后一天的价值

场景描述： 表示具体某天状态的数据（如库存数、员工实际人数或存货的日清算值）需要与可以聚合的指标（例如销售额或利润）区别对待。处理这些数据时，可能希望显示日历月最后一天的值。此外，我们可能希望从每月下钻到每周后，视图能够更新显示每周最后一天的值。

在下面的示例中，我们记录了每天多个时间点的库存数据。该视图对比了平均日清算值和周期最后一天的清算值。使用简单的详细级别表达式，我们可以向下查看每日级别，虽然数据直观显示的是较高级别。

可视化关键： 视图中日期维度（上面为月份）最后一天的闭市价格（close value）

Tableau LOD

可视化日期详细级别中中，最后一天的闭市价格。

最后一天：{ INCLUDE:MAX([Date])} , 视图中拖入了日期，颗粒度设置为月份，INCLUDE没有添加维度，也就是月份的最后一天

最后一天的闭市价格：IF { INCLUDE:MAX([Date])}=[Date] THEN [Adj Close] ELSE 0 END

BI LOD

最后一天 Max Date

{ FIXED DATE_FORMAT(`date`, 'yyyy-MM') : max(`date`) }

计算字段，最后一天的闭市价格, 拖入指标，选择MAX/MIN，：

IF(`date` = #{Max Date}, `adj close`, 0)

或者使用汇总计算字段

sum(IF(`date` = `Max Date`, `adj close`, 0))

10. 各个阵列的回头客

场景描述： 争取新客户的成本可能非常高，因此我们希望能够确保现有客户会重复购买。在一个、两个、三个、N 个季度重复购买的客户数目是多少？从未重复购买的客户数目是多少？如果按季度阵列划分重复购买行为，会怎么样？我们可以使用 FIXED 表达式找到每个客户的第一次和第二次购买日期，并从中计算出重复购买的季度数。

可视化关键： 订单日期与顾客两次购买间隔，对应的顾客数量

Tableau LOD

顾客两次购买的时间间隔。

首次购买日期：{FIXED [Customer ID]:MIN([Order Date])}

剔除首次购买日期：IIF([Order Date]> [首次购买日期],[Order Date],NULL)

第二次购买日期：{ FIXED [Customer ID]: MIN([剔除首次购买日期])}

计算两次消费日期间隔：DATEDIFF(‘quarter’,[首次购买日期],[第二次购买日期])

BI LOD

顾客两次购买的时间间隔。

首次购买日期 1st Purchase：

{FIXED `customer id` : min(`order date`)}

剔除首次购买日期 Repeat Purchase：

IF(`order date` > #{1st Purchase}, `order date`, null)

第二次购买日期 2nd Purchase, ：

{FIXED `customer id` : min(#{Repeat Purchase})}

计算两次消费日期间隔 Quarter to repeat purchase：

DATEDIFF('quarter', #{1st Purchase}, #{2nd Purchase})

热力图

列维 首次购买时间转换为季度

CONCAT(YEAR(#{1st Purchase}), '-Q', QUARTER(#{1st Purchase}))

行维 使用计算字段[Quarter to repeat purchase]

12. 相对周期筛选

场景描述： 与前一年的年初至今和月初至今进行对比是分析业绩的常用指标。通过筛选相对于今天，我们能够轻松得出分析，但如果数据每周都刷新一次，这时该怎么办？ 假设您最近一次刷新是在 3 月 1 日，但当前日期是 3 月 7 日。月初至今对比会显示前一年 3 月 1 日到 3 月 7 日与今年的 3 月 1 日的对比。 这会导致出现严重警报，但这种情况下不该出现警报！我们可以采用简单的详细级别表达式找出数据集中的最晚日期。

可视化关键： 今年同比去年的利润, 按周、月同比时，最后一个周期的天数，要根据数据日期在最后一个周期的天数决定

Tableau LOD

缺少去年YTD的截止日期，需要从今年的max日期中做比照。

Day of Year of Max Date = DATEPART(‘dayofyear’, {MAX([Order Date])} )
Day of Year of Order Date = DATEPART(‘dayofyear’, [Order Date] )
筛选字段  
Period Filter = [Day of Year of Max Date] >= [Day of Year of Order Date]

BI LOD

Day of Year of Max Date = DAYOFYEAR({FIXED  : max(`order date`)})
Day of Year of Order Date = DAYOFYEAR(`order date`)
-- 筛选字段
Period Filter = #{Day of Year of Max Date} >= #{Day of Year Order Date}

两年的日期均截止8-23日

13. 用户登录频率

场景13：用户登录频率 场景描述： 每月一次、每两月一次、每三月一次（依次类推）登录网站或应用程序的用户数目是多少？平均登录率是多少？这一平均值周围的分布偏斜如何？ 数据粒度为每个用户 ID 的登录日期。也就是说，用户登录的每一天都记录为一行。生成此视图需要按照登录率划分客户数，即我们必须按照某个度量划分另一个度量。 我们在示例 1 中了解了如何使用详细级别表达式轻松进行此类分析。

可视化关键： 首次和二次登录的间隔，对应的用户数量

Tableau LOD

首次和二次登录间隔，以及每个用户的登录次数。

首次登录日期+最近登录日期， fixed+min/max函数：
{FIXED [User ID]:MIN([Log in Date])}、{FIXED [User ID]:MAX([Log in Date])}

计算上述两个日期的间隔，用datediff函数，同时参数定为month：
DATEDIFF(‘month’,[首次登录日期],[最近登录日期])

计算每个用户的登录次数，fixed +countd：
{FIXED[User ID]:COUNTD([Log in Date])}

计算每个用户的平均登录周期，间隔/次数：
ROUND([间隔日期]/[每个客户的登录次数])

参考线，所有用户的平均登录周期：
{`{EXCLUDE [平均登录周期]:AVG([平均登录周期])}`}

柱子的渐变颜色：
AVG([平均登录周期])-AVG([所有用户的平均登录周期])

BI LOD

首次登录日期+最近登录日期， fixed+min/max函数：
{FIXED `User ID` : MIN(`Log in Date`)}、{FIXED `User ID`:MAX(`Log in Date`)}

计算上述两个日期的间隔，用datediff函数，同时参数定为month： 
Total Months User is Active = DATEDIFF('month', #{1st Login Date}, #{Last Login Date})

计算每个用户的登录次数，fixed +countd：
Total Months User is Active = {'{'}FIXED `User ID`: DISTINCT(`Log in Date`){'}'}

计算每个用户的平均登录周期，间隔/次数：
Log in Frequency = ROUND(#{Total Months User is Active} / #{Number of logins per user})

参考线，所有用户的平均登录周期：
Average Log in Frequency = {'{'}EXCLUDE `Log in Frequency` : avg(`Log in Frequency`){'}'}

柱子的渐变颜色：
max(`Log in Frequency`) - ${Average Log in Frequency}

14 成比例笔刷

场景描述： 任何分析中最基本的问题都是：“对比对象是什么？” 在筛选时，我们有时会需要对比所选内容和总量，而不是简单地向下筛选所选内容。这种技巧称为按比例笔刷。

可视化关键： 国家在每个分类的销售的占比

LOD Tableau

全部的销售数量（聚合级别更高）

该子分类在所有国家的销售额：{Fixed [Sub-Category] : Sum([Sales]) }

占比：SUM([sales])/SUM([该子分类在所有国家的销售额])

BI LOD

-- 该子分类在所有国家的销售额：
{Fixed [Sub-Category] : Sum([Sales]) }

选定国家销售额：
IF(`country`='${country!"China"}', `sales`, 0)

选择进度占比图，值轴选定为水平轴