在qlik sense 脚本中滚动计算所有客户近3个月的下单金额和SKU数量

背景

笔者因实际业务需求，每个月都要统计客户近3个月以来的下单金额、下单SKU，结合指标计算不同会员等级的客户数量，并按月度形成趋势图。

在Qlik的图表中，可通过表达式统计“指定日期”的客户下单金额、下单SKU，并计算会员等级和对应的数量，但无法做到同时统计不同月度的情况。

因此需要通过Qlik的脚本实现，生成一张客户在不同月份下单情况的数据表。

思路&目的

笔者通过搜索，在Qlik社区上找到类似的问题和答案：

Solved: Last 3 month sales value in Script - Qlik Community - 118220

问题是需要客户的订单记录，计算客户近3个月的订单金额；解答者的思路是通过IntervalMatch函数，构建日期区间来实现滚动式统计。

IntervalMatch函数的官方功能定义如下：

IntervalMatch ‒ Qlik Sense Enterprise on Kubernetes

（由于接下来就将展示脚本步骤，此函数就不再介绍，感兴趣的同学可自行阅读官方定义。）

笔者参考社区上分享的脚本，进一步制作了统计近3个月客户下单SKU和下单金额的脚本，以下是步骤和说明。和之前的分享一样，笔者也希望能抛砖引玉，获得更多更好的想法。

脚本步骤

1.构建样例数据

样表包括4个字段：客户，下单SKU，下单金额，下单日期。

data:

LOAD * INLINE [
customercode, SKU, Amount, OrderDate
A, apple, 50, 2022/1/1
A, banana, 30, 2022/2/5
A, apple, 40, 2022/3/21
A, pear, 60, 2022/4/1
A, peach, 55, 2022/5/17
A, cherry, 10, 2022/6/15
A, orange, 70, 2022/7/2
B, apple, 50, 2022/6/1
B, banana, 30, 2022/7/2
B, apple, 40, 2022/8/5
B, pear, 60, 2022/9/1
B, peach, 55, 2022/10/17
B, cherry, 10, 2022/11/15
B, melon, 88, 2022/12/2
];

这样构建了一个客户每天购买的SKU以及金额的数据表（这里特意将A客户设置为只在2022年1-6月下单，B客户只在7-12月下单），如下图：

图1

2.将数据按年月进行聚合

实际上可直接在第一步就将样本数据设置为客户每月的下单数据，但考虑到实际情况大多为按天统计，故而在脚本中进行聚合操作。

首先提取下单年月，并通过客户号+下单年月生成了一个关联键，用于之后的关联；另外，通过monthend函数求得了每个订单日期对应的“当月最后一天”，便于之后计算3个月前的日期。此步骤也可合并至第一步，简化脚本。

data_add_month:
Load
distinct customercode,
SKU,
Amount,
year(OrderDate)*100+month(OrderDate) as orderym,
text(customercode)&text(year(OrderDate)*100+month(OrderDate)) as linkkey,
monthend(date(OrderDate)) as last_date
Resident data;
Drop Table data;

接着，按照年月对数据进行聚合。

databymonth:
Load
Distinct customercode,
orderym,
last_date,
linkkey,
SKU,
sum(Amount) as orderamount
Resident [data_add_month]
Group By customercode, orderym, last_date, SKU, linkkey
Order By customercode, orderym, last_date, SKU, linkkey asc;
Drop Table data_add_month;

生成图表如下：

图2 

3.提取订单日期的年月，生成日历表

由于第2步的聚合，此日历表只包含了年月，这样可以控制数据量，提升计算效率。

*如订单日期对应的月份不完整，会导致计算结果不完整，因此笔者认为用其他方式生成日历表较好；后续如找到简洁的主日历表脚本，会更新文章。

yearmonth:
Load
distinct orderym,
last_date
Resident databymonth;

日历表如下图：

图3

4.客户+日期交叉

将下单客户和日历表关联，形成"客户+日期"的交叉表（暂称交叉表1），并通过客户和日期这两个字段形成连接主键。

Join yearmonth:
Load
distinct customercode
Resident databymonth;

orderbydate:
Load
*,
text(customercode)&text(orderym) as linkkey
Resident yearmonth;

Drop Table yearmonth;

生成图表如下:

 图4

5.关联客户订单信息

把客户的订单信息关联至交叉表1。客户无订单的日期，订单信息会显示为空值。

Left Join orderbydate:
Load
linkkey,
SKU,
orderamount as SKUamount
Resident databymonth;

Drop Table databymonth;

生成图表如下：

图5

6.获取近3个月的起-止日期

通过monthstart函数，生成近3个月的开始和结束日期，并通过区间函数形成日期的交叉表（暂称交叉表2）。

recent3m:
Load Distinct
Monthstart(AddMonths(last_date,-2)) as StartDate,
Date(last_date) as EndDate,
last_date AS LinkDate
Resident orderbydate;

Inner Join IntervalMatch (last_date)
Load
StartDate,
EndDate
Resident recent3m;

 生成图表如下：

图6.1

注意这里是33行，由于订单年月是2022年全年，共12个月；而统计的区间是每个月向前推3个月（含当月）， 1-10月对应3对“起-止日期”，而11月只对应2对“起-止日期”（2022/9/1-2022/11/30，2022/10/1-2022/12/31，再往下由于不存在2023年的数据所以没有了）；12月只对应1对“起-止日期”（2022/10/1-2022/12/31）。总行数=10*3+2+1=33行。

 图6.2

7.合并交叉表

把交叉表1和交叉表2合并，关联交叉表1的客户订单信息至交叉表2。

Join (recent3m)
Load
last_date,
customercode,
SKU,
SKUamount

Resident orderbydate;

生成图表如下：

图7

表的行数为66，实际上是日期交叉表的行数（=33）*客户数（=2）*客户每月下单SKU数（=1）。如果客户在一个月内下单了多个SKU，则行数会相应增长。

8.得出近3个月的下单数据

对交叉表2进行聚合，得出客户在每月对应的近3月下单SKU和下单金额。

recent3maggr:
Load
LinkDate,
customercode,
count(distinct SKU) as Recent3mSKU,
sum(SKUamount) as Recent3mAmount
Resident recent3m
Group by LinkDate, customercode
Order by LinkDate, customercode;

结果如下图：

 图8

此聚合表的行数为24，等于所有年月（12）*客户数（2）。可以看到，客户还没有开始下单的月份，近3个月的下单SKU和金额均为0。

9.最终整合

将上一步生成的聚合表关联至第5步生成的客户订单表，得到客户近3月的下单SKU和下单金额。

客户订单表包含了下单SKU列，如不需要也可以按实际情况再进行整合。

Left Join (orderbydate)
Load
LinkDate as last_date,
customercode,
Recent3mSKU,
Recent3mAmount
resident recent3maggr;

drop tables recent3m, recent3maggr;

最终得出的数据表如下图：

图9

至此，客户每月的下单金额、下单SKU数和近3月的下单金额、下单SKU数就全部整合完毕了。由于要统计SKU数，因此需要把“客户下单的所有SKU”也作为一个字段，相应的增加数据表的行数。

以上脚本也可按日期进行聚合，如：统计近60天的下单情况，但这样会导致数据量剧增，运算压力增大，建议考虑实际情况后再自行调整脚本。