This can be achieved by using different techniques. Let me try a more Bayesian one.
These are some simple steps:
// Step 1: Data Preparation
// 1.1 Load Data: Ensure you have your sales data loaded into Qlik Sense. Your data should include at least these fields (or something like it)
- TransactionID
- ItemID
- Date
// 1.2. Create Date Fields: Depending on your analysis period (monthly, yearly), you can create additional fields. Look at the Community for Master Calendar for more details.
SalesData:
LOAD
TransactionID,
ItemID,
Date,
Year(Date) as Year,
Month(Date) as Month,
Year(Date) & '-' & Month(Date) as YearMonth
RESIDENT SalesData;
// Step 2: Data Aggregation
// 2.1. Cross Join Data: Create a cross join to get combinations of items sold together.
CrossJoin:
LOAD
TransactionID,
ItemID as ItemA
RESIDENT SalesData;
JOIN (CrossJoin)
LOAD
TransactionID,
ItemID as ItemB
RESIDENT SalesData
WHERE ItemID <> ItemA;
// 2.2. Calculate Frequencies: Aggregate the data to calculate the frequencies of combinations.
FrequencyTable:
LOAD
ItemA,
ItemB,
YearMonth,
COUNT(DISTINCT TransactionID) as Count
RESIDENT CrossJoin
GROUP BY ItemA, ItemB, YearMonth;
FrequencyTableYearly:
LOAD
ItemA,
ItemB,
Year,
COUNT(DISTINCT TransactionID) as Count
RESIDENT CrossJoin
GROUP BY ItemA, ItemB, Year;
// Step 3: Probability Calculation
// 3.1. Calculate Total Transactions: Calculate the total number of transactions for each item and time period.
TotalTransactions:
LOAD
ItemID,
YearMonth,
COUNT(DISTINCT TransactionID) as TotalCount
RESIDENT SalesData
GROUP BY ItemID, YearMonth;
TotalTransactionsYearly:
LOAD
ItemID,
Year,
COUNT(DISTINCT TransactionID) as TotalCount
RESIDENT SalesData
GROUP BY ItemID, Year;
// 3.2. Join Total Transactions: Join the total transaction data to your frequency table.
LEFT JOIN (FrequencyTable)
LOAD
ItemID as ItemA,
YearMonth,
TotalCount as TotalCountA
RESIDENT TotalTransactions;
LEFT JOIN (FrequencyTable)
LOAD
ItemID as ItemB,
YearMonth,
TotalCount as TotalCountB
RESIDENT TotalTransactions;
LEFT JOIN (FrequencyTableYearly)
LOAD
ItemID as ItemA,
Year,
TotalCount as TotalCountA
RESIDENT TotalTransactionsYearly;
LEFT JOIN (FrequencyTableYearly)
LOAD
ItemID as ItemB,
Year,
TotalCount as TotalCountB
RESIDENT TotalTransactionsYearly;
// 3.3. Calculate Probability: Add a field to calculate the probability of two items being sold together.
FrequencyTable:
LOAD
*,
(Count / (TotalCountA + TotalCountB - Count)) as Probability
RESIDENT FrequencyTable;
FrequencyTableYearly:
LOAD
*,
(Count / (TotalCountA + TotalCountB - Count)) as Probability
RESIDENT FrequencyTableYearly;
// Step 4: Visualization in Qlik Sense
4.1. Create a Pivot Table: Add a pivot table to your Qlik Sense sheet.
4.2. Add Dimensions: Use `ItemA` and `ItemB` as dimensions.
4.3. Add Measures: Use `Probability` as the measure to show the probability of items being sold together.
// Step 5: Filter by Time Period
Add filters for `Year` and `Month` to allow dynamic analysis based on different time periods.
This approach should work for a lot of cases. I have not tested this specific script and since I wrote from heart, you might need to adjust here and there, but it gives you the idea of how to perform basic Bayesian theorem in Qlik.
Check out my latest posts at datavoyagers.net
