Qlik Sense 中计算最近两个月数据的月环比

目的

以月为单位，动态监测客户最近两个月的下单情况（金额/订单数/SKU数等），计算出月环比，并结合当月的时间进度标注异常值。

基础数据

客户主数据表；表结构为：客户ID, 客户姓名

订单表；表结构为：客户下单日期，下单年月，下单金额，客户ID

Qlik步骤

1.构建变量

第一个变量为：当前年月 (currentym)；使用今天所属的年乘以100倍，加上今天所在的月份，可得到一个六位数字。如，今年为2023年，本月为7月，则当前年月为：2023*100+7=202307

Let currentym = year(date(today()))*100 + month(date(today()));

第二个变量为：前一年月 (lastym)；使用上个月所属的年乘以100倍，加上个月的月份；

我们首先使用MonthStart(today()),得出今天所属月的第一天，再减去一天，就可得到上个月的最后一天（嵌套MonthEnd）。如，本文撰写日期为2023年7月29日，7月第一天是7月1日，再向前一天是6月30日，即上个月的最后一天。前一年月为：2023*100+6=202306

Let lastym = year(MonthEnd(MonthStart(today())-1))*100 + Month(MonthEnd(MonthStart(today())-1));

第三个变量为：当月的时间进度 (timeprocess)；使用今天所属月份的天数，除以今天所属月份的总天数，如本文撰写日期为2023年7月29日，即第29天，7月共31天，则时间进度为：29/31=93.5%（四舍五入）

Let timeprocess = day(today())/day(monthend(today()));

2. 引入数据

通过Load * Inline直接引入客户主数据和订单数据。

Customer:
Load * Inline [
CustomerID, CustomerName
A, Adam
B, Bruce
C, Coco
D, Diana
E, Eddie
];

SalesData:
Load * Inline [
Sales_Date, YM, ID, Sales
2023/5/4, 202305, A, 50
2023/5/12, 202305, B, 60
2023/5/23, 202305, E, 70
2023/6/10, 202306, A, 100
2023/6/12, 202306, A, 80
2023/6/12, 202306, B, 200
2023/6/17, 202306, B, 50
2023/6/19, 202306, C, 35
2023/7/10, 202307, A, 60
2023/7/11, 202307, C, 200
2023/7/22, 202307, C, 20
2023/7/27, 202307, D, 50
2023/7/30, 202307, D, 90
2023/8/5, 202308, A, 88
2023/8/12, 202308, E, 40
];

数据效果如图：

 

3.计算本月及上月的销售额

本文直接将上月销售额和当月销售额左联接至客户主数据，实际应用中可视场景而定；

左联接的方法就是将客户每天的销售额以“上一年月”和“当前年月”分别进行聚合，只需让年月等于之前构造的变量即可。对于没有销售额的情况，可将空值进一步调整为"0"。

//lastymsales
Left Join (Customer) Load
ID as CustomerID,
YM as lastym,
Sum(Sales) as lastymsales
Resident SalesData
Where YM = '$(lastym)'
Group By ID, YM;

//currentymsales
Left Join (Customer) Load
ID as CustomerID,
YM as currentym,
Sum(Sales) as currentymsales
Resident SalesData
Where YM = '$(currentym)'
Group By ID, YM;

//adjustsales
Adjsales:
Load
CustomerID,
lastym,
currentym,
If(isnull(lastymsales),0,lastymsales) as lstymsales,
If(isnull(currentymsales),0,currentymsales) as crtymsales
Resident Customer;
Drop Table Customer;

数据结果如图：

 

4.计算月环比

通常月环比的数值，是直接用本月销售额除以上月销售额，再减去1；这里考虑到存在一些小于等于0的情况（如出现售后逆向金额），调整如下：

上月销售额	本月销售额	月环比
<0	<=0	0%
=0	=0	0%
=0	>0	100%
>0	<=0	-100%
>0	>0	(本月/上月)-1

//month on month
mononmontmp:
Load
*,
Num(
If(lstymsales<0,if(crtymsales<=0,0,1),
If(lstymsales=0,if(crtymsales=0,0,if(crtymsales>0,1,-1)),
If(lstymsales>0,if(crtymsales<0,-1,crtymsales/lstymsales-1))))
,'#.#%') as salesmononmon
Resident Adjsales;
Drop Table Adjsales;

数据结果如图：

 

5.计算预警标签

根据上一步计算出的月环比，结合当月时间进度的变量，计算标签是否为”预警“。

我们这里使用了30%作为阈值，如月环比增长大于等于30%或月环比下降小于等于-30%，则需要预警，否则为正常。考虑到时间进度的影响，阈值调整为”30%乘以时间进度“。当然，阈值可根据实际情况灵活调整。

mon_on_mon:
Load
*,
Num($(timeprocess),'#.#%') as timeprocess,
If(salesmononmon>=0.3*$(timeprocess) or salesmononmon <= -0.3*$(timeprocess),'预警','正常') as sale_status
Resident mononmontmp;
Drop Table mononmontmp;

数据结果如图：

 

至此月环比及预警标签的脚本就制作完毕了。