一种在Qlik Sense 中计算两个年月所覆盖时间跨度的方法以及场景应用

背景：笔者在一个B2b电商平台从事数据分析工作，公司选取Qlik Sense作为BI分析工具；当前的业务需求是分析每个月份的客户下单情况，这需要得出每个客户的注册年月、下单年月以及下单月份数。由于Qlik Sense在国内的资料较少，笔者在工作之余总结、撰写此文，旨在分享并盼能抛砖引玉，得到更多、更好的想法，不仅可以用于Qlik，也可在其他BI工具实践。

一、计算方法

设: 开始年=Fy, 开始月=Fm, 开始年月=Fym

设: 结束年=Ly, 结束月=Lm, 结束年月=Lym

年月=Year * 100 + Month

默认: Ly >= Fy; Fm & Lm ∈ [1,12]

Time Span = {[(13-Fm) + Lm] + (Ly – Fy -1)*12}

Or: {[(Fy*100 + 13) – Fym] + (Lym – Ly*100) + (Ly-Fy-1)*12}

13-Fm: 开始年当年月份数。从当月开始至12月，共13-Fm个月，当月本身也计算在内（如9月-12月，共4个月；12月-12月，共1个月）

Lm: 结束年当年月份数。从1月至结束月，共Lm个月，结束月本身也计算在内（如1月-5月，共5个月，1月-8月，共8个月）

(Ly-Fy-1)*12: 从开始年至结束年的月份数

考虑年份差别，共有三种情况。

1.如开始年和结束年为同一年，Ly=Fy, 则时间跨度= 13-Fm+Lm-12, 即 Lm-Fm+1，结束月减去开始月+1；

2.如结束年为开始年的下一年，Ly = Fy+1, 则时间跨度 = 13-Fm+Lm, 即开始年的所有月份数量(13-Fm)加上结束年的所有月份数量Lm；

3.如结束年比开始年晚两年及以上，则时间跨度 = 开始年当年月份数+结束年当年月份数 + (开始年与结束年相隔年份的所有月份数)

二、场景应用

计算客户注册年月与当前下单年月的月份数，并与客户在当前下单年月的‘总下单月份数’进行比较，计算客户对于平台的忠诚度

三、Qlik脚本步骤

出于数据隐私性，本文使用编撰的数据进行演示说明，并尽可能的精简了数据。

需要的基础数据：订单列表，客户注册信息表

客户注册信息表字段：customer_code; customer_name; register_date

订单列表字段：order_date; order_number; customer_code; commodity; order_amount

以下所有的计算均在Qlik Sense的脚本编辑器完成。

(1) 加载订单列表数据：

[orderlist]:

Load * Inline [

order_date, order_number, customer_code, commodity, order_amount

2021/1/5, O2021001, T001, Milk, 10.5

2021/1/20, O2021002, T001, Cheese, 5

2021/3/2, O2021003, T001, Ham, 20

2021/3/7, O2021004, T002, Apple, 8

2021/4/10, O2021005, T001, Banana, 9

2021/5/18, O2021006, T001, Pie, 30

2021/6/1, O2021007, T001, Coke, 7

2021/6/1, O2021008, T002, Butter, 6

2021/6/1, O2021009, T003, Bone, 18

2021/7/1, O2021010, T003, Steak, 100

2021/8/8, O2021011, T003, Bacon, 60

2021/8/12, O2021012, T001, Egg, 40

2021/8/20, O2021013, T002, Candy, 15

2021/9/6, O2021014, T003, Potato, 22

2021/10/1, O2021015, T003, Bean, 20

2021/10/15, O2021016, T001, Chocolate, 12

2021/11/11, O2021017, T003, Pork, 70

2021/11/17, O2021018, T002, Chicken, 30

2021/12/10, O2021019, T002, Bread, 14

2021/12/12, O2021020, T001, Fish, 56

2021/12/15, O2021021, T003, Ketchup, 24

2022/1/1, O2021022, T003, Jam, 35

];

(2) 加载客户注册信息表，并与订单列表进行左关联：

Left Join [orderlist]:

Load * Inline [

customer_code, customer_name, register_date

T001, Tom, 2021/1/1,

T002, Jerry, 2021/3/1,

T003, Spike, 2021/6/1

];

合并后的数据表如下图：

 

(3) 根据合并后的订单列表 [orderlist]，得出每个客户的注册年，registeryear, 注册年月registerym，用数字形式表现，即registeryear*100+registermonth; 同理，得到每个客户的下单年orderyear和下单年月orderym

[orderwidetable]:

Load *,

Year(register_date) as [registeryear],

Year(register_date)*100 + Month(register_date) as [registerym],

Year(order_date) as [orderyear],

Year(order_date)*100 + Month(order_date) as [orderym]

Resident [orderlist];

Drop Table [orderlist];

生成的数据表如下图：

 

(4) 基于第三步生产的宽表 [orderwidetable]，以客户维度聚合一张新表，求每个客户的：首次下单年firstyear；首次下单年月firstym；总下单月数totalordermonth

[ordermonth]:

Load

[customer_code] as [ccode],

[registerym] as [rym],

[registeryear] as [ryear],

min(orderym) as [firstym],

min(orderyear) as [firstyear],

count(distinct [orderym]) as [totalordermonth]

Resident [orderwidetable]

Group By [customer_code],[registerym],[registeryear];

生成的数据表如下图：

 

 (5) 同样基于 [orderwidetable]，以客户和下单年月维度聚合一张新表，求每个客户的每个不同的下单年月

[count]:

Load

[customer_code] as [ccode],

[orderym] as [oym],

[orderyear] as [oyear],

count(distinct orderym) as [countym]

Resident [orderwidetable]

Group By [customer_code],[orderym],[orderyear]

Order By [customer_code],[orderym];

生成数据表如下图：

 (6) 将第四步生成的表左关联到第五步生成的表，这样，每一行的客户下单年月，都能显示客户的首单年份、首单年月、总下单月数

Left Join [count]:

Load

*

Resident [ordermonth];

Drop Table [ordermonth];

生成数据表如下图：

 

(7) 基于第六步生成的表格，应用时间跨度计算方法，求每个客户的每个下单年月与首单年月的间隔月数 [lifemonth]，并计算每个客户在每个下单年月时的累计下单月份数 [ordermonth]

求累计下单月数参考了Qlik Community的一篇文章，主要使用了Peek函数

链接：Solved: Script: cumulative sum + group by - Qlik Community - 1810410

[count_temp]:

Load

*,

(([ryear]*100+13-[rym])+([oym]-[oyear]*100)+(([oyear]-[ryear]-1)*12)) as [lifemonth],

if(RowNo()=1,[countym],if(ccode<>Peek(ccode),[countym],if(ccode = Peek(ccode) and Peek([ordermonth]) = 1, 2, [countym]+Peek([ordermonth])))) as [ordermonth]

Resident [count]

Order By [ccode],[oym];

Drop Table [count];

生成数据表如下图：

 

(8) 基于第七步生成的累计下单月数 [ordermonth] 和 注册至今月数 [lifemonth]，计算客户在每个下单年月的忠诚度 [loyal%]

[count_final]:

Load

*,

Num([ordermonth]/[lifemonth],'#,##0%') as [loyal%]

Resident [count_temp];

Drop Table [count_temp];

Rename Table [count_final] to [count];

生成数据表如下图：

 

这样就得到了每个客户在每个下单年月的忠诚度。

笔者也在Qlik Community 投稿了英文版，有兴趣请戳此链接