计算字段示例
为了演示计算字段的功能,特意采用超市订单数据的原始形式,比如销售额是字符串¥ 111,而不是处理过后的数字 111。
分析超时订单发货时间间隔与销售额的关系
首先创建一个计算字段,叫做sales
,为去除销售额的货币符号,并将其转换为数值,采用计算公式:
FLOAT(REPLACE([销售额],’¥’,’’))
其中REPLACE
函数将货币符号替换为空,FLOAT 函数将数据转换为数值形式。
接着创建一个计算 字段,叫做发货间隔天数
,计算公式为:
DATEDIFF(‘day’,[订单日期],[发货日期])
其中,使用DATEDIFF
函数计算订单开始到货物发出去的天数间隔。
将sales
拖动到行,发货间隔天数
拖动到列,将发货间隔天数设置为维度,离散,如下图
在基本图表中选择可视化探索器,得到如下图表:
可以看到,发货间隔与销售额在 0-4 存在一个先是总体递增,然后在 4-8 之间递减的这么一个关系,也可以看出所有订单都会 8 天之前发货完成。这个图表反映了超市发货的效率,超市可以根据这个数据适当提升发货的速度。如果能配合用户满意度数据,则可以在此基础上反映发货间隔与用户满意度之间的关系。
分析超时各月的销售额增长情况
首先,为得到各月的增长情况,需要用本月的销售额与上月的做对比(月环比),可以采用计算字段的LOOKUP
函数,该函数可以返回目标行的值。
先将销售额在分区中向下偏移一个位置,公式为:LOOKUP(SUM([sales]),-1)
,将上 月的销售额跟本月的销售额在分区中对齐,随后创建一个计算字段,叫做:销售额环比,计算公式为:
SUM([sales]) - LOOKUP(SUM([sales]),-1)
将订单日期拖动到行,将销售额环比拖动到列,订单日期的时间拆分设置为月,如下图
在基本图表中选择可视化探索器,得到如下图表:
可以看直观地看到每个月的增长情况,为负数,说明本月销售额低于上一月,不止如此,如果设置订单日期的时间拆分为年,则反映的是年环比,季度等类似。
由于使用了 LOOKUP 导致第一条数据为空,这倒也没什么影响,如果用户想第一条数据保留原始值,则可以在LOOKUP
外面再使用一个ZN
函数,该函数遇到空值会返回 0,其他情况返回原始值,完整的公式是:
SUM([sales]) - ZN(LOOKUP(SUM([sales]),-1))
。这样,第一条数据就保留了原始值。
更加丰富的时间拆分功能
时间拆分功能提供了一个快速拆分时间的快捷方式,但是如果该功能不满足需求,我们可以自己创建计算字段来实现。
如要进行周环比,我们可以这样:
1、创建 计算字段 [订单日期_周],计算公式为:DATEFORMAT([订单日期],’yyyy年第ww周’)
,但是,需要注意的是,这样查出来的是每年每周的数据,一般来说,数据量会很多,不适合展示。那么如果我们只是想对比周而不考虑年,则格式化字符串可以修改为ww周
或者使用计算公式:DATEPART(‘week’,[订单日期])
。
2、将发货间隔天数拖动到维度,并配置相应的设置如排序等即可。注意的是,使用 LOOKUP 等表计算函数,必需要设置服务端排序,否则结果可能不如预期。
按秩排序
使用普通排序或者服务器排序功能可以使得数据按顺序排列,但是有时候我们不关心其具体的数值大小,之关系其大小关系,并且如果数值差异较大,则数值较小的部分难以看出差距,这时我们可以使用按秩排序的功能,也即是使用RANK
这个函数。
首先我们创建一个计算字段,叫做:销售额的秩,计算公式:
RANK(SUM([sales]))
将该计算字段拖动到列,其余步骤与前面相同,得到图表如下:
按秩百分比排序
同按秩排序类似,如果想求本期的销售额要好于多少 其他期的百分比,可以使用 RANK_PERCENTILE 函数。 首先创建一个计算公式,叫做:销售额秩百分比,计算公式为:
ROUND(RANK_PERCENTILE(SUM([sales])) * 100,2)
得到如下图表,可以直观地看到本期的销售额要好于其他多少期的比例。
分析各类别的销售额总计在各销售额区间的比例
新建一个计算字段:销售额按 100 分段,计算公式为:FLOOR([sales]/100)
。将该字段拖动到维度,可以得到如下结果:
可以看到,销售额被分成了 10 个区段,每个区段范围为 100。更进一步,我们希望上图的数字的含义更为明确,我们可以修改计算公式为:
CONCAT(FLOOR([sales]/100)*100,’-‘,(FLOOR([sales]/100)+1)*100)
得到的结果如下:
接着,可以将 销售额按100分段
拖动到列,再将销售额拖动到行,然后将类别拖动到颜色标记,为了防止数据量过大造成卡顿,可以设置限额为 100。
此时,将得到如下堆叠图
将鼠标移动到堆叠条上,可以直观地看到,各数据分段种各类别的销售额占比。
这个功能的核心就是使用FLOOR
函数,该函数会将数据向下取整,当使用 [sales]/100
分段后(这步操作不会减少数据条数),再使用FLOOR
函数向下取整,原本 [0-1)
之间的数值都归约到 0, [1-2)
之间的数值都归约到 1,以此类推,这样就实现了数据分段的功能。同时再配合 CONCAT
字符串连接函数,可以自定义显示的文本内容。