背景：筆者在一個(gè)B2b電商平臺(tái)從事數(shù)據(jù)分析工作，公司選取Qlik Sense作為BI分析工具；當(dāng)前的業(yè)務(wù)需求是分析每個(gè)月份的客戶(hù)下單情況，這需要得出每個(gè)客戶(hù)的注冊(cè)年月、下單年月以及下單月份數(shù)。由于Qlik Sense在國(guó)內(nèi)的資料較少，筆者在工作之余總結(jié)、撰寫(xiě)此文，旨在分享并盼能拋磚引玉，得到更多、更好的想法，不僅可以用于Qlik，也可在其他BI工具實(shí)踐。

一、計(jì)算方法

設(shè): 開(kāi)始年=Fy, 開(kāi)始月=Fm, 開(kāi)始年月=Fym

設(shè): 結(jié)束年=Ly, 結(jié)束月=Lm, 結(jié)束年月=Lym

年月=Year * 100 + Month

默認(rèn): 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: 開(kāi)始年當(dāng)年月份數(shù)。從當(dāng)月開(kāi)始至12月，共13-Fm個(gè)月，當(dāng)月本身也計(jì)算在內(nèi)（如9月-12月，共4個(gè)月；12月-12月，共1個(gè)月）

Lm: 結(jié)束年當(dāng)年月份數(shù)。從1月至結(jié)束月，共Lm個(gè)月，結(jié)束月本身也計(jì)算在內(nèi)（如1月-5月，共5個(gè)月，1月-8月，共8個(gè)月）

(Ly-Fy-1)*12: 從開(kāi)始年至結(jié)束年的月份數(shù)

考慮年份差別，共有三種情況。

1.如開(kāi)始年和結(jié)束年為同一年，Ly=Fy, 則時(shí)間跨度= 13-Fm+Lm-12, 即 Lm-Fm+1，結(jié)束月減去開(kāi)始月+1；

2.如結(jié)束年為開(kāi)始年的下一年，Ly = Fy+1, 則時(shí)間跨度 = 13-Fm+Lm, 即開(kāi)始年的所有月份數(shù)量(13-Fm)加上結(jié)束年的所有月份數(shù)量Lm；

3.如結(jié)束年比開(kāi)始年晚兩年及以上，則時(shí)間跨度 = 開(kāi)始年當(dāng)年月份數(shù)+結(jié)束年當(dāng)年月份數(shù) + (開(kāi)始年與結(jié)束年相隔年份的所有月份數(shù))

二、場(chǎng)景應(yīng)用

計(jì)算客戶(hù)注冊(cè)年月與當(dāng)前下單年月的月份數(shù)，并與客戶(hù)在當(dāng)前下單年月的‘總下單月份數(shù)’進(jìn)行比較，計(jì)算客戶(hù)對(duì)于平臺(tái)的忠誠(chéng)度

三、Qlik腳本步驟

出于數(shù)據(jù)隱私性，本文使用編撰的數(shù)據(jù)進(jìn)行演示說(shuō)明，并盡可能的精簡(jiǎn)了數(shù)據(jù)。

需要的基礎(chǔ)數(shù)據(jù)：訂單列表，客戶(hù)注冊(cè)信息表

客戶(hù)注冊(cè)信息表字段：customer_code; customer_name; register_date

訂單列表字段：order_date; order_number; customer_code; commodity; order_amount

以下所有的計(jì)算均在Qlik Sense的腳本編輯器完成。

(1) 加載訂單列表數(shù)據(jù)：

[orderlist]:Load * Inline [order_date, order_number, customer_code, commodity, order_amount2021/1/5, O2021001, T001, Milk, 10.52021/1/20, O2021002, T001, Cheese, 52021/3/2, O2021003, T001, Ham, 202021/3/7, O2021004, T002, Apple, 82021/4/10, O2021005, T001, Banana, 92021/5/18, O2021006, T001, Pie, 302021/6/1, O2021007, T001, Coke, 72021/6/1, O2021008, T002, Butter, 62021/6/1, O2021009, T003, Bone, 182021/7/1, O2021010, T003, Steak, 1002021/8/8, O2021011, T003, Bacon, 602021/8/12, O2021012, T001, Egg, 402021/8/20, O2021013, T002, Candy, 152021/9/6, O2021014, T003, Potato, 222021/10/1, O2021015, T003, Bean, 202021/10/15, O2021016, T001, Chocolate, 122021/11/11, O2021017, T003, Pork, 702021/11/17, O2021018, T002, Chicken, 302021/12/10, O2021019, T002, Bread, 142021/12/12, O2021020, T001, Fish, 562021/12/15, O2021021, T003, Ketchup, 242022/1/1, O2021022, T003, Jam, 35];

(2) 加載客戶(hù)注冊(cè)信息表，并與訂單列表進(jìn)行左關(guān)聯(lián)：

Left Join [orderlist]:Load * Inline [customer_code, customer_name, register_dateT001, Tom, 2021/1/1,T002, Jerry, 2021/3/1,T003, Spike, 2021/6/1];

合并后的數(shù)據(jù)表如下圖：

?

(3) 根據(jù)合并后的訂單列表 [orderlist]，得出每個(gè)客戶(hù)的注冊(cè)年，registeryear, 注冊(cè)年月registerym，用數(shù)字形式表現(xiàn)，即registeryear*100+registermonth; 同理，得到每個(gè)客戶(hù)的下單年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];

生成的數(shù)據(jù)表如下圖：

?

(4) 基于第三步生產(chǎn)的寬表 [orderwidetable]，以客戶(hù)維度聚合一張新表，求每個(gè)客戶(hù)的：首次下單年firstyear；首次下單年月firstym；總下單月數(shù)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];

生成的數(shù)據(jù)表如下圖：

?

?(5) 同樣基于 [orderwidetable]，以客戶(hù)和下單年月維度聚合一張新表，求每個(gè)客戶(hù)的每個(gè)不同的下單年月

[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];

生成數(shù)據(jù)表如下圖：

?(6) 將第四步生成的表左關(guān)聯(lián)到第五步生成的表，這樣，每一行的客戶(hù)下單年月，都能顯示客戶(hù)的首單年份、首單年月、總下單月數(shù)

Left Join [count]:Load*Resident [ordermonth];Drop Table [ordermonth];

生成數(shù)據(jù)表如下圖：

?

(7) 基于第六步生成的表格，應(yīng)用時(shí)間跨度計(jì)算方法，求每個(gè)客戶(hù)的每個(gè)下單年月與首單年月的間隔月數(shù) [lifemonth]，并計(jì)算每個(gè)客戶(hù)在每個(gè)下單年月時(shí)的累計(jì)下單月份數(shù) [ordermonth]

求累計(jì)下單月數(shù)參考了Qlik Community的一篇文章，主要使用了Peek函數(shù)

鏈接：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];

生成數(shù)據(jù)表如下圖：

?

(8) 基于第七步生成的累計(jì)下單月數(shù) [ordermonth] 和 注冊(cè)至今月數(shù) [lifemonth]，計(jì)算客戶(hù)在每個(gè)下單年月的忠誠(chéng)度 [loyal%]

[count_final]:Load*,Num([ordermonth]/[lifemonth],'#,##0%') as [loyal%]Resident [count_temp];Drop Table [count_temp];Rename Table [count_final] to [count];

生成數(shù)據(jù)表如下圖：

?

這樣就得到了每個(gè)客戶(hù)在每個(gè)下單年月的忠誠(chéng)度。

筆者也在Qlik Community 投稿了英文版，有興趣請(qǐng)戳此鏈接

總結(jié)

以上是生活随笔為你收集整理的一种在Qlik Sense 中计算两个年月所覆盖时间跨度的方法以及场景应用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。