📓Nhúng Nhu Liệu

Trong phần này, chúng ta sẽ tiếp tục vẽ biểu đồ thu nhập của cộng tác viên theo từng khoảng thời gian. Cụ thể chúng ta muốn biết thu nhập của 3 ngày trước, tuần trước, tháng trước, quí trước và năm trước.

Rõ ràng dữ liệu cần thiết để vẽ đồ thị này không thể lấy được từ bộ nhu liệu bên dưới báo cáo, mà đang lấy tổng số tiền theo từng chương trình. Bạn cần số liệu giao dịch theo ngày. Nếu bạn kéo thêm cột ngày vào lưới kết xuất, thì tổng số tiền sẽ được tính toán theo tổ hợp giữa chương trình và ngày. Và khi có nhiều hơn một quyên góp xảy ra vào những ngày khác nhau trong cùng một chương trình, thì số lượng hàng dữ liệu trong kết quả sẽ bị tăng lên. Và như vậy, sẽ phá hỏng báo cáo bạn đã thiết lập cho đến nay.

Giải pháp đơn giản là chúng ta sẽ tạo ra một báo cáo mới để tập hợp dữ liệu theo cách khác. Ví dụ chúng ta sẽ chỉ lấy số tiền, không lấy tổng, và ngày của tất cả các giao dịch quyên góp cho các chương trình gây quỹ mà cộng tác viên đã quảng bá. Sau đó, copy và nhúng bộ nhu liệu bên dưới của nó vào báo cáo hiện hành.

Ngoài ra, để tiết kiệm tính toán, khi tập hợp nhu liệu chúng ta cũng muốn tính luôn số hoa hồng cộng tác viên nhận được trên mỗi giao dịch, bằng cách nhân tỷ lệ hoa hồng với số tiền quyên góp và thêm vào trong dữ liệu kết xuất.

Quá trình này được gọi là tiền xử lý dữ liệu thô.

Tiền Xử Lý

Trước tiên hãy tạo một báo cáo tên là ONEIRO_AF_CP, và gọi nó là Quảng Bá & Thu Nhập, có cùng nguồn nhu liệu là ONEIRO_AF_TX.

Nguồn nhu liệu ONEIRO_AF_TX chứa tất cả các giao dịch của tất cả các chương trình cho tất cả cộng tác viên. Bạn cần lọc ra để chỉ thu thập các giao dịch cho một cộng tác viên có địa chỉ ví nhất định.

Ngoài ra, bạn cần lấy thông tin số tiền và ngày quyên góp, và hoa hồng sẽ được tính bằng số tiền quyên góp nhân cho tỷ lệ hoa hồng (affiliate_fee). Thực hiện các bước sau:

  1. Kéo cốt địa chỉ ví của cộng tác viên (address) vào lưới bộ lọc phía trên và tạm thời nhập giá trị 0x78a02bb94b324bd2d671ba32f644530b55a0271e vào ô trong cột Giá trị.

  2. Kéo cột ngày giao dịch (transaction_date) bên dưới ONEIRO_DT_TX vào lưới kết xuất.

  3. Kéo cột số tiền (amount) bên dưới ONEIRO_DT_TX vào lưới kết xuất.

Để tính trực tiếp hoa hồng, hãy kéo cột số tiền một lần nữa vào lưới kết xuất. Sau đó, bên cột Bí Danh:[Công Thức] nhập công thức sau:

commission:ONEIRO_DT_TX.amount * ONEIRO_DT_CP.affiliate_fee / 100

Khi bạn chạy báo cáo, bộ dữ liệu sẽ trông giống như sau:

Công Thức

Nói thêm về các công thức tiền xử lý. Ngoài các các toán tử thông thường, KNOWINS hỗ trợ các hàm xử lý dữ liệu thông dụng. Dưới đây là một số ít hàm tiêu biểu:

HàmCú phápGiải thích

ABS

ABS( column )

ABS( expression )

Trả về giá trị tuyệt đối của một cột hoặc một biểu thức

ROUND

ROUND( expression , length)

Làm tròn giá trị với số lẻ xác định bởi tham số length

FLOOR

FLOOR( column )

FLOOR( expression )

Trả về số nguyên lớn nhất nhỏ hơn hoặc bằng giá trị của biểu thức

CONCAT

CONCAT( string1 , string2 [ , stringN ] ... )

Nối các chuỗi lại với nhau

LEFT

LEFT( string, N )

Trả về N ký tự bên trái của chuỗi ký tự.

RIGHT

RIGHT( string, N )

Trả về N ký tự bên phải của chuỗi ký tự.

SUBSTR

SUBSTRING( string, start, N )

Trả về chuỗi ký tự bên trong từ vị trí bắt đầu và kéo dài N ký tự.

TRIM

TRIM( string )

Xóa các khoảng trắng trước và sau chuỗi ký tự.

COALESCE

COALESCE( expression [ ,...n ] )

Trả về giá trị đầu tiên khác NULL.

NULLIF

NULLIF( expression , expression )

Trả về NULL nếu hai giá trị bằng nhau, ngược lại trả về giá trị đầu tiên.

DISTINCT

Alias: DISTINCT col1, col2

Lấy ra tổ hợp các giá trị duy nhất của các cột col1, col2…

Lưu ý:

  • Nếu được sử dụng, DISTINCT phải là hàng duy nhất có trong lưới kết xuất.

  • Tên bí danh (Alias) được gán cho cột cuối cùng, nếu có nhiều cột.

SUM

SUM( column )

SUM( expression )

Tính tổng. Nếu được sử dụng, SUM phải là hàng duy nhất trong lưới kết xuất, hoặc tất cả các hàng còn lại trong lưới kết xuất cũng được tính với SUM, AVG, COUNT, MAX, MIN.

AVG

AVG( expression )

Tính trung bình. Nếu được sử dụng, AVG phải là hàng duy nhất trong lưới kết xuất, hoặc tất cả các hàng còn lại trong lưới kết xuất cũng được tính với SUM, AVG, COUNT, MAX, MIN.

COUNT

COUNT( column )

Đếm. Nếu được sử dụng, COUNT phải là hàng duy nhất trong lưới kết xuất, hoặc tất cả các hàng còn lại trong lưới kết xuất cũng được tính với SUM, AVG, COUNT, MAX, MIN.

MAX

MAX( expression )

Tìm giá trị lớn nhất. Nếu được sử dụng, MAX phải là hàng duy nhất trong lưới kết xuất, hoặc tất cả các hàng còn lại trong lưới kết xuất cũng được tính với SUM, AVG, COUNT, MAX, MIN.

MIN

MIN( expression )

Tìm giá trị nhỏ nhất. Nếu được sử dụng, MIN phải là hàng duy nhất trong lưới kết xuất, hoặc tất cả các hàng còn lại trong lưới kết xuất cũng được tính với SUM, AVG, COUNT, MAX, MIN.

Nếu bạn biết về SQL, bạn có thể sử dụng tất cả các hàm xử lý dữ liệu của SQL tương thích với nguồn dữ liệu (MS SQL, MariaDB, MySQL, Postgres, Oracle) của bạn cho các công thức tiền xử lý.

Nhúng Nhu Liệu

Tiếp theo chúng ta sẽ "nhúng" bộ nhu liệu mới tạo vào báo cáo thu nhập để lấy số liệu vẽ đồ thị. Nhớ lại trong bảng cấu hình, cột A là nơi định nghĩa các bộ nhu liệu và cột B là nguồn dữ liệu của các bộ nhu liệu đó. KNOWINS hỗ trợ lệnh USER TABLE cho phép bạn nhúng các bộ nhu liệu từ bên ngoài vào trong báo cáo theo hai bước đơn giản:

Bước 1: Nhấn vào nút Dataset để copy công thức tạo nhu liệu của báo cáo Quảng Bá & Thu Nhập, ONEIRO_AF_CP, vào clipboard. 

USER TABLE(QUERY_DB({"from":"ONEIRO_AF_TX","select":[{"node":"ONEIRO_AF_TX\\ONEIRO_DT_TX\\created_on","name":"transaction_date","alias":"","datatype":"D","aggregate":"","expression":"","sorting":""},{"node":"ONEIRO_AF_TX\\ONEIRO_DT_TX\\amount","name":"amount","alias":"","datatype":"N","aggregate":"","expression":"","sorting":""},{"node":"ONEIRO_AF_TX\\ONEIRO_DT_TX\\amount","name":"amount","alias":"commission","datatype":"N","aggregate":"","expression":"amount * affiliate_fee / 100","sorting":""}],"where":[{"node":"ONEIRO_AF_TX\\address","name":"address","alias":"","datatype":"","filterfrom":"0x78a02bb94b324bd2d671ba32f644530b55a0271e","filterto":"","isnot":"N","operator":"WALLET"}]},ONEIRO_AF_CP))

Nếu bạn tò mò thì công thức trông như sau (USER TABLE dùng để khai báo một bộ nhu liệu nhúng, và QUERY_DB để lấy dữ liệu từ nguồn dữ liệu):

USER TABLE(QUERY_DB(
{
  "from": "ONEIRO_AF_TX",
  "select": [
    {
      "node": "ONEIRO_AF_TX\\ONEIRO_DT_TX\\created_on",
      "name": "transaction_date",
      "alias": "",
      "datatype": "D",
      "aggregate": "",
      "expression": "",
      "sorting": ""
    },
    {
      "node": "ONEIRO_AF_TX\\ONEIRO_DT_TX\\amount",
      "name": "amount",
      "alias": "",
      "datatype": "N",
      "aggregate": "",
      "expression": "",
      "sorting": ""
    },
    {
      "node": "ONEIRO_AF_TX\\ONEIRO_DT_TX\\amount",
      "name": "amount",
      "alias": "commission",
      "datatype": "N",
      "aggregate": "",
      "expression": "amount * affiliate_fee / 100",
      "sorting": ""
    }
  ],
  "where": [
    {
      "node": "ONEIRO_AF_TX\\address",
      "name": "address",
      "alias": "",
      "datatype": "",
      "filterfrom": "0x78a02bb94b324bd2d671ba32f644530b55a0271e",
      "filterto": "",
      "isnot": "N",
      "operator": "WALLET"
    }
  ]
}
,ONEIRO_AF_CP))

Công thức này sẽ lấy từ bộ nhu liệu ONEIRO_AF_TX (from: ...) các cột transaction_date, amount và commission ̣(select: ...) , theo các điều kiện lọc ̣(where: ...).

Bước 2: Bây giờ hãy quay lại mở file Excel mẫu báo cáo của báo cáo thu nhập. Đi vào bảng <#Config> và dán công thức này vào một ô trong cột B, ví dụ B16. Đặt tên cho bộ nhu liệu lấy từ công thức này là Transaction trong ô A16.

Hậu Xử Lý

Đến đây bạn đã có được dữ liệu bao gồm ngày tháng, số tiền quyên góp và hoa hồng được nhúng vào báo cáo thu nhập dưới tên bộ nhu liệu Transaction. Các dữ liệu này đã được lọc theo địa chỉ ví của cộng tác liên nên tổng số tiền hoa hồng sẽ chính là hoa hồng của cộng tác viên đó.

Tiếp theo chúng ta cần tính tổng số tiền hoa hồng theo các khoảng thời gian cho 3 ngày trước, tuần trước, tháng trước, quí trước và năm trước để lấy số liệu vẽ đồ thị.

Chúng ta sẽ sử dụng lại thẻ hàm tính gộp có điều kiện Aggregate để thực hiện điều này. Đi đến bảng <#Config> và định nghĩa các thẻ hàm sau:

M21: Earnings3
N21: <#Aggregate(SUM;Transaction;<#Transaction.commission>;DAYS(NOW(),<#Transaction.transaction_date>) < 3)>

M22: Earnings7
N22: <#Aggregate(SUM;Transaction;<#Transaction.commission>;DAYS(NOW(),<#Transaction.transaction_date>) < 7)>

M23: Earnings30
N23: <#Aggregate(SUM;Transaction;<#Transaction.commission>;DAYS(NOW(),<#Transaction.transaction_date>) < 30)>

M24: Earnings90
N24: <#Aggregate(SUM;Transaction;<#Transaction.commission>;DAYS(NOW(),<#Transaction.transaction_date>) < 90)>

M25: Earnings365
N25: <#Aggregate(SUM;Transaction;<#Transaction.commission>;DAYS(NOW(),<#Transaction.transaction_date>) < 365)>

M26: EarningsGT365
N26: <#Aggregate(SUM;Transaction;<#Transaction.commission>;DAYS(NOW(),<#Transaction.transaction_date>) < 999999999)>

Giải thích:

  1. Ô M21 xác định một thẻ tên Earnings3.

  2. Ô N21 là định nghĩa của thẻ tên này: Nó dùng thẻ hàm <#Aggregate(...)> để duyệt qua các hàng của bộ nhu liệu và áp dụng hàm tính gộp TỔNG (SUM), cho giá trị trong cột số tiền hoa hồng (commission) của bộ nhu liệu Transaction.

    • DAYS là hàm của Excel dùng để tính số ngày cách biệt giữa hai ngày. NOW() là một hàm khác của Excel trả về giá trị của ngày hôm nay. Trong trường hợp này, hàm DAYS tính số ngày khác biết giữa hôm nay và ngày xảy ra giao dịch (transaction_date)

    • Thẻ hàm hiểu được các hàm của Excel bên trong nó. Khi áp dụng hàm tính gộp SUM, thẻ hàm Aggregate sẽ lọc ra chỉ lấy các dữ liệu thỏa mãn điều kiện số ngày khác biệt không quá 3 ngày: DAYS(NOW(),<#Transaction.transaction_date>) < 3

    • Kết quả là thẻ hàm Earnings3 sẽ tính được tổng số tiền hoa hồng cộng tác viên kiếm được trong 3 ngày gần nhất

  3. Tương tự như vậy cho các thẻ hàm còn lại Earnings7, Earnings30, Earnings90, Earnings365, EarningsGT365.

Để vẽ đồ thị bạn cần phải sắp xếp các dữ liệu này vào một bảng dữ liệu trong mẫu báo cáo. Hãy chèn một bảng mới, đặt tên là DATA vào trong mẫu báo cáo Excel, và tạo mật dải tên EarningsByPeriod như sau:

Giải thích:

  1. Cột A1 và B1 là tiêu đề của hai cột giá trị dùng cho đồ thị. Cột A sẽ được dùng làm giá trị cho trục X, và cột B cho trục Y.

  2. Dải các ô từ A1:B7 sẽ là bảng dữ liệu để vẽ đồ thị, được định nghĩa bằng dải tên EarningsByPeriod.

Vẽ đồ thị

Đi vào bảng trình bày của báo cáo và trộn dải các ô từ C10 đến D22 lại để lấy làm vị trí sẽ vẽ đồ thị. Nhắp chuột phải trên ô vừa trộn và chọn New Note để thêm vào một lệnh vẽ đồ thị, ERN_PRD là tên của đồ thị:

CHART:ERN_PRD

Cuối cùng, quay lại báo cáo thu nhập, nhấn vào biểu tượng đồ thị và thêm vào một đồ thị có tên là ERN_PRD và dải dữ liệu là EarningsByPeriod, sau đó chọn loại đồ thị và giá trị các trục như hướng dẫn trong bài trước.

Lưu và chạy lại báo cáo để kiểm tra kết quả.

Có một vấn đề đối với bộ nhu liệu Transaction. Đó là nó được lọc để chỉ lấy các giao dịch liên quan đến cộng tác viên có địa chỉ ví là 0x78a02bb94b324bd2d671ba32f644530b55a0271e. Chúng ta muốn báo cáo này phải chạy đúng cho bất kỳ cộng tác viên nào. Nói cách khác, nếu người dùng nhập một địa chỉ khác vào lưới bộ lọc, thì bộ nhu liệu Transaction phải lấy các giao dịch liên quan đến địa chỉ ví đó.

Nhớ lại là chúng ta đã định nghĩa thẻ Affiliate để lấy vào địa chỉ ví của cộng tác viên từ lưới bộ lọc. Bạn có thể thay thẻ này vào địa chỉ ví cộng tác viên trong công thức nhúng nhu liệu để giải quyết vấn đề đó.

Lưu và tải mẫu báo cáo mới lên. Sau đó chạy với những địa chỉ ví cộng tác viên khác nhau để so sánh kết quả.

Để thuận tiện, bạn có thể tài mẫu báo cáo đã hoàn thiện về tại đầy:

PreviousĐồ ThịNextLiên Kết

Last updated