- Calculate method
Let: Start Year=Fy, Start Month=Fm, Start Year-Month=Fym
Let: End Year=Ly, End Month=Lm, End Year-Month=Lym
Year-Month = Year * 100 + Month
Default: Ly >= Fy; Fm & Lm ∈ [1,12]
Formula: Time Span = {[(13-Fm) + Lm] + (Ly – Fy -1)*12}
Or: {[(Fy*100 + 13) – Fym] + (Lym – Ly*100) + (Ly-Fy-1)*12}
(13-Fm): Month number of Start Year. There are (13-Fm) months from Start Month to December, including the Start Month itself. For example, it counts a total of 4 months from September to December (13-9=4); it counts a total of 1 month from December to December (13-12=1).
Lm: Month number of End Year. There are Lm months from January to end month, including the End Month itself. For example, it counts a total of 5 months from January to May; it counts a total of 8 months from January to August.
(Ly-Fy-1)*12: Month number of Start Year to End Year.
Considering the year difference, there exist three situations.
1. If the Start Year and the End Year are the same year (Ly=Fy), then Time Span = (13-Fm)+Lm+(-1)*12, that is, (Lm–Fm+1), the End Month minus the Start Month and plus one. This equals to the number of months from Start Month to End Month.
2. If the End Year is the next year of the Start Year (Ly=Fy+1), then Time Span = (13-Fm)+Lm, that is the number of months in the Start Year (13-Fm) plus the number of months in the End Year (Lm).
3. If the End Year is two or more years later than the Start Year, then Time Span = the number of months in the Start Year + the number of months in the End Year + (the number of all months in the year between the Start Year and the End Year)
- Scenario Application
Calculate the number of months between customer’s registration year-month and each order year-month, and compare it with the customer’s [total number of order months] corresponding to each order year-month, so as to calculate the customer’s loyalty to the platform
- Qlik Sense Load Script Steps
Due to data privacy, this article uses dummy data for demonstration.
Basic data: customer registration information, order list.
Customer registration information fields：customer_code; customer_name; register_date
Order list fields：order_date; order_number; customer_code; commodity; order_amount
(1) Load order list data.
[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) Load customer registration information, and left join it to order list.
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
];
The combined table is shown as below.
(3) According to the merged [orderlist], get each customer’s: registration year (registeryear); register year-month (registerym), which is expressed in digital form, registeryear*100+registermonth; in the same way, get the order year (orderyear) and order year-month (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];
Table generated is shown as below.
(4) Based on the wide table [orderwidetable] generated in the step 3, generating a new table by customer to get each customer’s: first order year (firstyear), first order year-month (firstym), total number of order months (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];
Table generated is shown as below.
(5) Based on the same table [orderwidetable], generating a new table by customer and order year-month to get each customer’s different order year-month.
[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];
Table generated is shown as below.
(6) Left join table generated in step 4 to the table generated in step 5. Therefore, each table row will be able to show customer’s first order year, first order year-month and total order months.
Left Join [count]:
Load
*
Resident [ordermonth];
Drop Table [ordermonth];
Table generated is shown as below.
(7) Based on the table generated in step 6, apply the time span calculation method to get each customer’s number of months between each order year-month and first order year-month ([lifemonth]). Using Peek Function to get the cumulative order months in each customer’s order year-month ([ordermonth]).
Calculating cumulative order year-month mainly refers to an article below.
Link: https://community.qlik.com/t5/QlikView-App-Dev/Script-cumulative-sum-group-by/td-p/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];
Table generated is shown as below.
(8) Based on the [ordermonth] and [lifemonth] generated in step 7, calculating customer loyalty ([loyal%]) in each order year-month.
[count_final]:
Load
*,
Num([ordermonth]/[lifemonth],'#,##0%') as [loyal%]
Resident [count_temp];
Drop Table [count_temp];
Rename Table [count_final] to [count];
Table generated is shown as below.
In this way, the loyalty of each customer in each order year and month is calculated.