作为Tableau学习路上最大的拦路虎之一,LOD如此强大,但又如此变化莫测,需要反复的练习和琢磨才行。在LOD的第三篇中,我们详细介绍了分析LOD类型命题的思路,即可视化焦点+引用背景的分解方法(【Tableau】详细级别表达式LOD_(3))。这篇博客,我们将详细地介绍官方的15大详细级别表达式,并用上面的思路做进一步的分解,有助于初学者更好地理解这些案例。

下面的链接,可以看到LOD15大详细级别表达式的中英文版原文。

「可视化焦点+引用背景」的分解方法,其本意就是让我们把解决问题分为台前和幕后两个阶段,台前的部分是数据冰上的可视化部分,而幕后的部分则是使用LOD引用可视化级别中没有的聚合。这就是官方博客中第二段的首句的意思。

“LOD表达式提供了一种简单的方法,用以计算不在可视化详细级别层面的数据聚合。你因此可以在视图中以多种方式整合这些数据。”

LOD Expressions provide a way to easily compute aggregations that are not at the level of detail of the visualization. You can then integrate those values within visualizations in arbitrary ways.

好的,我们接下来会结合官方15大详细级别表达式,介绍一下这15个案例。

 

1、客户订单频率 Customer order frequency

在零售分析中,我们经常会遇到“顾客购买频次”分析,通过查看顾客购买次数的数量和分布,来分析顾客的复购黏性,

命题:客户的购买频率分布,即购买过一次、两次、三次……的顾客分别多少。

  • 可视化焦点:不同购买次数下的顾客数量。
    • 可视化维度:购买频次(1,2,3,N)
  • 需要引用的背景信息:每个顾客的购买次数
    • fixed [customer ID] : countd(order)

在视图中,我们仅使用两个维度——购买频次和顾客的计数,也就是两个都是数值。用官方的方式说,这是用一个数值去区分另一个数值(breaking out a measure by another measure ),LOD fixed可以轻松地把一个度量转化为维度,这是include和exclude不能做的。

1 顾客购买品牌.png

//要点:Fixed LOD的独特之处是返回的多个数据结果可以作为维度使用。相比之下,include和exclude都只能作为度量使用。

 

2、阵列分析 cohort  analysis

这个命题希望看到每个年度的新增客户在当年的销售贡献。我们把顾客获得的年度,简单称之为顾客新增,或者叫新增年度。从视图的角度看,视图的关注焦点是什么?每年中不同顾客阵列的贡献金额。因此,可视化中的维度是年、销售额sum和顾客标签(新增年度)。

在视图中我们缺少什么信息,需要我们从背景信息中引用呢?我们缺少每个顾客的标签(新增年度),因为视图不是顾客详细级别的,这就需要LOD来实现不在视图详细级别的维度的聚合。(LOD provide a way to easily compute aggregations that are not in the level of the visulization.)

首先我们按照客户首次购买的年份将客户分组,虽然这个LOD fixed返回的是首次购买的完整日期,但是我们使用它的年度部分,因此把所有客户按照年度分为几组,比如2010、2011、2012……

1st order date =  {Fixed [customer ID]: MIN([order date])}

为了对比各个阵列的年度销售贡献额,我们把order year拖入列,profit加入行,生成一个可视化图之后,再引用背景信息(首次购买日期)到颜色中,形成不同年度客户的贡献金额。注意,这个视图的详细级别是顾客,因此一个顾客只会对应一个最早购买日期。

Screen Shot 2018-11-26 at 09.43.39.png

接下来的工作,就是通过表计算实现阵列的百分比分析了。

//要点:一方面使用了fixed LOD作为维度使用的功能,另一方面通过把LOD字段加入到颜色标记,进一步增加了数据的表现深度。

 

3、每日利润KPI / Daily Profits KPI

How many days each month are high profitable, profitable, or unprofitable?命题是每月的高盈利、盈利和亏损天数分别有多少? 还是按照可视化分析焦点和引用的额外背景来分析。

可视化是一个矩阵,它的分析焦点是月份、天数(countd [days])。问题是,这里的天数是我们需要提供的背景信息,按照指定的标准,把每天都打上特定的标签,比如利润大于2万标记为‘高盈利’。

  • 可视化焦点:各个月份中,不同盈利标签的“天”的数量
  • 额外引用的背景:根据盈利的要求,给每天返回一个盈利标签。

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

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

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

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

这样可视化就拥有了需要的各项信息:月份、盈利标签(Daily Profit KPI)、天。

Screen Shot 2018-11-26 at 10.16.25.png

4. 总额百分比 Percent of total

这个应该是最容易的一个,用到我们在LOD系列第一篇中讲到的最简单的LOD表达式:行级别LOD表达式,也就是省略了维度生命的fixed 表达式,比如说 { sum([sales]) },它会返回所有sales的总额,不管在视图中有什么筛选器(上下文筛选器除外)和维度层级。

这个例子是说,如果我们从聚焦全球到聚焦欧洲,但是依然想看欧洲各国在全世界销售额的百分比,而不仅仅是占欧洲的百分比,怎么办?

这里涉及到LOD系列第二篇中讲到的“Tableau操作顺序”,因为总额百分比属于表计算,而表计算的操作顺序优先级低于各种筛选器,因此,当我们从聚焦全球到聚焦欧洲,欧洲各国的总额百分比就从全球占比改为了欧洲占比。解决这个问题,需要将总额百分比的分母锁定,也就是提升到维度筛选器之前,因此就可以使用fixed LOD表达式,而最简单的Fixed LOD就是 {sum([sales]) }  ——省略了维度声明的Fixed 详细级别表达式。

  • sum([sales]) 返回指定维度下的销售额;
  • { sum([sales]) } 返回所有区域的销售额,不管视图中的区域多少;由于{ sum([sales]) }返回的是行级别的数值(数据源的每个行都会一个{ sum([sales]) }的字段),因此适用于需要先聚合。

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

5. 新客户争取率 New customer acquisition

命题:what is the toal number of customers we’ve acquired per market by day?

这个命题的可视化焦点是每个市场每天的新客户获得数量,因此需要把三个维度或度量加入到视图:日期(天)、市场、新顾客数量。 注意,这个题目的数据,应该是订单级别的,而不是商品级别的。

而新顾客数量,就是我们需要使用LOD额外引用的背景信息。

要特别注意的是,这个题目和前面第二题不同,前面我们通过 {fixed customer: MIN(order date) }返回每个客户的最早订单日期,因为第二题的详细级别是顾客,因此顾客和最早购买日期是一一对应的。但是这个命题中,查看客户争取数量,是以日期为详细级别的,一个顾客的订单可以分布在多个日期之中,因此就需要对应不同的标签。这是这个命题的关键。

因此,额外的背景信息就是,在每个顾客每天对应的标签,是新客户,还是老客户?判断新老客户,又需要与首次购买日期做对比,因此首先会用到Fixed LOD:

1st order date =  {Fixed [customer ID]: MIN([order date])

之后将每一次的订单时间和首次购买时间做匹配,给每个订单打标签

IFF( [1st order date]=[order date], ‘new’,’existing’ )

在准备好了背景数据之后,我们就可以构建视图了,这里把上面的new/existing加入了筛选器,仅保留了新客户的数据,也就是只有每天新客户的首次订单。既然一个新客户只对应一个订单,就可以通过通过计算顾客的数量,加上running total,生成新客户的数量。

Screen Shot 2018-11-26 at 12.12.05.png

//要点:在上一个的用例基础上,这个增加了if判断、筛选器和表计算。而Fixed LOD只有一个。这个的难点在于理解命题的整个逻辑。

 

6. 对比销售额分析  Comparative sales analysis

How do the Sales of all my Categories compare to that of a selected Category?

//Dec 11, 2018 update

这个是这里面为数不多要用exclude LOD的。这里面的一个关键是,把可视化层面的选择通过参数传给计算字段,然后用以在每个单元格上与原有数据对比计算。

  • 可视化的焦点:每个category的销售额,减去选定的category的销售额。

参数是选择category,因此在category右键新建一个parameter即可,然后显示参数控制器(show parameter control)。如何在选择参数之后,生成视图中的一个数值呢?我们创建一个计算字段:selected Category

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

上面的计算看上去容易,但是当我们直接使用 sum(category-sales)- sum(selected category-sales)时,会发现,虽然是聚合计算,但是尽在每个单元格上有效,每一行的sum(selected category-sales)没有锁定。sum(selected category-sales)只有在选定的category上是有意义的,其他地方都是0,也就是else的数值。如下图:

6-lod.png

解决这个问题,需要引用在每一行上引用一个额外的背景信息:不与视图中category字段相关的选定行的合计,也就是category字段不能影响计算 [selected category]的汇总,这就需要从视图层面exclude数据。可以参考下面的图片:

6 LOD -2.png

7. 各个销售代表的平均最大额交易数额 Average of top deals by sales rep

命题:what is the top closed by sales rep, and the the average of these deals by contry?

这是非常典型的一个LOD题目,按照区域计算各销售代表的最大订单金额的平均值,我们区分一下两个层面:

  • 可视化的焦点:不同地区的“最大订单金额”的平均值 ,维度是地区,度量是“最大订单金额”自定义字段,
  • 额外引用的背景数据:销售代表的最大订单金额,使用max函数,但是要指定销售代表这一维度,如果不考虑区域,应该是是 { fixed [rep ID] : MAX([sales]) }

但是,因为有地图的区域,最终是要根据区域显示最大订单金额的,因此这里的LOD要把地图区域加入进来,因此我们使用include

include LOD:在视图中已有维度基础上,增加指定的维度进行计算

Screen Shot 2018-11-26 at 18.11.22

Fixed和include 差异很大。 fixed仅考虑指定的维度,而include则是可视化的维度加指定的维度;fixed LOD可以作为维度使用,而include只能作为度量;fixed的优先级高于维度筛选器,因此可以忽略维度筛选器的影响,这就是为什么具有总额的总额百分比使用fixed,而include的优先级低于维度筛选器。

如果这里,把上面的include 改为下面的fixed可以吗?

{fixed [country], [sales rep] : MAX ([sales)}

考虑到这里没有其他筛选器,这两个返回的结果就是一样的。

这个命题的关键,就是通过自定义字段Largest Sales Deal by Rep,为视图提供背景数据

Largest Sales Deal by Rep = {INCLUDE [Sales Rep]: MAX([Sales])}

8.实际对比目标 Actual vs. target

命题:What percentage of products are meeting their profit target in each state?

可以把这个看作是比较典型的LOD include的代表题目,说明的是如何在视图详细级别上,引用但不显示一个新维度参与计算

这个题目是要展示美国每个州的商品销售达成情况,视图中包含两个部分,其一是各州商品总达成与总目标的差异汇总,反映区域的销售总额完成度,其二是各州中完成计划的商品数量,在各州销售商品数量的占比,反映商品之间的平衡,确保不会因为几个商品的过高达成掩饰了其他商品的低业绩。

第一个视图非常简单,就是以州为详细级别,利润总额减去目标总额即可。

第二个视图的要点在于,视图中并没有出现商品的详细级别维度,但是要有以商品为详细级别计算,比如说阿拉巴马州,可能总的在售商品数量为10( countd[products]=10 ),但是完成销售目标的商品数量可能只有6  (判断[profit] – [target] >0 ,返回商品数量)。

虽然我们可以在视图之外通过计算字段完成逻辑上的所有计算

第一步,判断商品是否完成任务 difference = [profit] – [target] , IIF (difference>0, 1, 0)

第二步,计算完成任务的商品数量  countd ( IIF (difference>0, 1, 0)  )

第三步,把完成的商品数量除以总商品 countd ( IIF (difference>0, 1, 0)  ) / countd( product )

但是,逻辑上正确的计算,当我们拖入到视图中就会出问题,问什么?因为上面的所有计算都是以商品为详细级别的,但是视图中根本没有 商品 的维度!

如何解决这个问题,回到开篇的那句话,“LOD详细级别表达式为我们提供了一条简单的方法,用以计算不在可视化详细级别层面的数据聚合。你因此可以在视图中以多种方式整合这些数据。”在这里,如何引用但不显示,从而不影响已有的视图布局?考虑到最后的计算是在州的区域和商品两个维度基础上的,仅需要在目前的视图维度中,增加商品维度即可,因此

{include [product] :countd ( IIF ([difference]>0, 1, 0)  ) / countd( [product ]) }

//这一个完全没有演示,逻辑上说明白,希望大家联系一下。

9. 周期最后一天的价值 Value on the last day of a period

What is the close value of each stock on the last day of the month compared the average daily close value?Drill down on the date axis to compare these on a weekly or daily level.

这个是比较难以理解的题目。视图中希望查看两个数值的对比,其一是股市闭市价的平均值,这个实现起来简单,日期作为维度,close value取平均值就好,暂且不考虑多个stock,生成的视图如下图。

Screen Shot 2018-12-11 at 18.08.11.png

在此基础上,我们分析一下题目,希望上面的数值,与另一个数值对比:周期中最后一天的闭市价格。

  • 可视化的焦点:视图中日期维度(上面为月份)最后一天的闭市价格(close value)

问题在于,上面的视图详细级别是月份,“最后一天”并不在这个详细级别中,它的颗粒度更高。涉及到两个数据颗粒度、数据详细级别的问题,我们就需要使用LOD引用视图中没有的背景数据。即:

  • 视图中需要引用的背景数据:可视化日期详细级别中中,最后一天的闭市价格。这里视图的角度和引用的背景数据是一样的,因为最终的颗粒度都是day。

LOD会在数据源层面生成一个新字段,因此是行级别的,我们可以理解为是给每一行数据打上了一个辅助分析的数据标签。在这里,我们希望通过LOD,以视图中指定的日期字段为详细级别,找出这些日期的最大值(也就是最后一天)对应的闭市价,而其他日期的字段数据标记为0,字段如下:Close value on last day

if {INCLUDE : max([Date])} = [Date]  then [Adj Close]  else 0  end

这个LOD字段为每一行的数据都生成了一个数据,最后日期则为闭市价,其他日期则为零。

9-lod.png

我们这个时候把 平均值和最后一天两个字段加入视图,就可以形成我们想要的两条对比数据。

9-lod-2.png

这里需要把第二条线的默认sum改为avg,确认无误后,我们再加表示stock分类的字段加入,从而两条线,改为六条线。

9-lod-3.png

至此,这个就算完成了。当我们从month改为week或者其他维度时,颗粒度变化会导致数据的自动改变。 这里的关键是找到我们需要引用的数据——视图中日期维度的最大值,然后返回闭市价的标签。

10.各个阵列的回头客

After customers are acuqired, how many quarters elapse before they make another purchase?

在零售分析中,会员分析的RFM模型会被广泛引用,RFM分别代表会员最近一次消费(Recency)、消费频率(Frequency)、消费金额(Monetary),三者构成了衡量客户价值的重要指标。通过分析最近一次消费的时间可以判断会员状态(流失?沉睡?存活?),通过分析消费频次可以判断顾客的忠诚度,而通过分析消费金额,则可以推算顾客的贡献度(高价值顾客、低价值顾客等)。

在这个题目中,我们要分析顾客两次消费的间隔,因此我们需要给每位顾客分别打两个标签:最近一次消费时间、最近第二次消费时间。

我们在视图中构建矩阵,横轴和纵轴分别会是日期和日期间隔,数据为两次消费时间的间隔;视图中没有顾客的维度,但是又是以顾客为详细级别的。为了解决“视图中没有的维度但又要引用这个级别的数据计算”,我们需要使用fixed返回顾客的消费日期。

  • 可视化的焦点:下单日期和购买频次间隔,顾客详细级别。
  • 需要引用的背景信息:每个顾客在两次购买中间的间隔,顾客的订单详细级别。

这个题目的关键是,我们需要使用两次Fixed函数,返回两个日期,第二个日期建立在第一个日期的计算基础上。

首次消费日期:如果我们把每个顾客的所有消费日期看作一个整体,我们可以理解为一个数组;首次消费日期,就是从每个数组中找到其中的最小值。我们使用fixed指定每个顾客返回这个结果,因此公式如下:

1st purchase date = {Fixed [Customer ID] : MIN([Order Date])}

Fixed LOD是直接在数据源层面计算的,返回的结果是行级别的,因此每个顾客的每一条记录都对应了一个1st purchase date字段。

接下来,我们需要返回第二次购买时间,如何返回?我们需要使用一个临时字段,新建一个新的数组,剔除首次购买时间。我们可以使用IIF函数,把首次消费时间返回为NULL,其他的按照原值返回。公式如下:

IIF( {fixed [Customer ID] :min([Order Date])} =[Order Date], null, [Order Date])

如果创建了第一个字段,也可以使用上面的第一个字段替换,简化为:

IIF( [1st purchase date ]=[Order Date], null, [Order Date])

这个字段暂且命名为[Repeat Purchase],返回的是顾客剔除首次消费日期外的其他消费日期,再次基础上,我们再用fixed函数返回第二次消费日期即可,如果直接在上面的基础上修改,就是:

2nd purchase date = {Fixed [Customer ID] : MIN([Repeat Purchase])}

如果不使用中间临时字段,直接在上面的临时字段上修改,那么结果就是:

{ FIXED [Customer ID]:min(IIF({fixed [Customer ID] :min([Order Date])}=[Order Date],null,[Order Date]))}

这样就生成了 第二次消费日期。

至此,我们就使用Fixed LOD返回了每个顾客的两次消费日期,接下来,我们需要使用datediff函数,以季度为参数,计算两次日期的间隔,然后生成视图。需要注意的是,视图中的日期,使用的是首次消费的日期。

Quarters to Repeat Purchase = datediff(‘quarter’,[1st Purchase],[2nd Purchase])

Screen Shot 2018-11-29 at 08.10.50.png

11. 一系列平均值的百分比差异

这个可以视为第六题的升级版,第六题对比的是平均值与最后一个日期的数值;这个题目是对比平均值和一个日期区间的平均值。另外,这里的日期粒度是最高颗粒度——准确日期。

我们需要几个主要的步骤:

  1. 通过参数输入开始日期和结束日期;
  2. 返回两个日期中间的数值;
  3. 返回每个stock的上述区间的平均值;——这里需要使用fixed LOD,给每个stock返回一个值
  4. 根据总的平均值和区间平均值,计算变化百分比 (A-B)/B

这里第一步和第二步相对而言比较简单,可以吧主要的参数和区间公式参考如下,可以看到在指定的区间中,我们截断了close value;接下来就要给每个stock返回它们的平均值。

11-lod.png

我们要给每个stock(ticker)返回一个数值,也就是把上面区间的数值求平均,就需要排除所有其他的维度,我们仅指定ticker本身,返回区间平均值:

{FIXED [Ticker]: AVG([Close value in reference period])}

之后,我们可以计算百分比差异了。

 

12. 相对周期筛选

(补充)早期对这个题目有误解,所以迟迟没有动笔。仔细看这个题目,刚上来,感觉有几个思路可以考虑,其一是按照week来做筛选器,把两年的日期都筛选到34周,这样人为把数据截断,其二是通过index表计算,筛选到想要的周。两个逻辑是一样的。

不过仔细看题目给出的一个假设情景,“假如最近一次刷新是3月1日,但当前日期是3月7日。”也就是,筛选不能以当前日期筛选,而应该以数据源的最后数据日期筛选。同样的道理,上面的题目不能通过周来筛选,否则很可能去年筛选了完整的34周,今年的8月23日只是到周四,这样计算YTD,看上去今年的34周对比去年的34周,但实际上最后一周的对比是不完整的。

因此,最好的方式是按照最细的数据颗粒度做筛选,准确地说,以数据源中的最后的日期作为筛选基准,筛选相对周期(比如去年)的最后对比日期。

既然要先从数据源找到最后的日期,就直接可以用max函数,{ max ([order date]) },这个相当于省略了维度的Fixed LOD,优先于维度筛选器和视图维度,返回的是所有order date的最后日期,比如说2014年8月23日。

但是以此为基准,和去年的日期做对比,从而精确的筛选出最大日期之前的所有数据,我们还需要去除年份的影响,因此需要用到datepart()函数——仅返回日期中指定的部分。嵌套上上面的LOD结果,这里用到了我之前没有用过的语法:

dayofyear = DATEPART(‘dayofyear’, {MAX([Order Date])} )

//注意,虽然我们说LOD返回的是数组,不过毕竟使用了MAX,这里只会返回一个数值,就是2014年8月23日,然后取当年的日期序号,返回235——这天是全年的第235天。

同样的逻辑,我们把它和2013年的order date的日期序号做比较,只保留小于等于235天的数值,即:

[dayofyear] >= DATEPART(‘dayofyear’, [Order Date] )   也就是下图中,仅保留蓝色线条以下的数据。

12-1

把上面的判断的结果(布尔值)加入筛选器,选择true结果,数据筛选就完成了。接下来的任务其实是表计算了。有两条折线图的是running total的累进表计算,另一个就是在此基础上二次表计算——两年的different from。

12-2

另,关于留言中咨询的对比不同开业时间的门店累计销售问题,不需要像上面考虑最后的数据源日期,重点是如何把起点统一到一个起点,其实就是表计算index擅长的了,不妨参考“10大表计算”的案例2部分。

13. 用户登录频率

这个题,和前面矩阵分析有点类似,其实考验的是理解能力了。我刚看这个题目就是一脸懵状。命题是:每月、两个月、三个月、N个月登录一次的用户占比分别是多少?

如何寻找每两个月登录一次的用户?官网给出的思路是通过最早一次和最后一次的间隔计算活跃周期,然后除以周期内的登录次数。嗯,是个办法。因此,我们需要几个字段:

  1. 首次登录日期+最近登录日期, fixed+min/max函数
  2. 计算上述两个日期的间隔,用datediff函数,同时参数定为month
  3. 计算每个用户的登录次数,fixed +countd
  4. 计算每个用户的平均登录周期,间隔/次数

遇到一个问题是,平均登录周期不规则小数,如果要改为数据直方图需要的数据桶,还需要round四舍五入为整数,然后建立数据桶。

13-.png

14. 成比例笔刷

这个LOD的题目相对而言不难,主要要点在于地图作为筛选器、双轴柱状图、Fixed LOD。

想要实现通过地图筛选国家,然后使用柱状图同步显示所选国家的各个子分类与该子分类在所有国家的销售,以及这两者的比例。生成地图在这里不在多讲,重点在于如何显示商品分类销售与所有国家分类。

14-.png

15、各个客户阵列的年度购买频率

注:我觉得这篇文章太长了😄 最后一个案例的解释尤其长。所以把会员分析相关的案例部分单独做一篇。最难的第15案例,放在了续篇,大家可以直接跳转查看:

 

LOD系列

  1. 【Tableau】详细级别表达式LOD_详尽入门3.0(1)
  2. 【Tableau】详细级别表达式LOD_位置与语法(2)
  3. 【Tableau】详细级别表达式LOD_终极实践指南与资源大全(3)
  4. 【Tableau】详细级别表达式 LOD 核心要点回顾(4)
  5. 【Tableau】LOD15大详细级别表达式-深度解读
  6. 【Tableau】15大详细级别表达式-会员分析案例

Nov 26, 2018
Nov 27, 2018
Dec 11, 2018 update
Jan 31, 2019 update