Find out the frequency of repetition of dimension value combinations – e.g. frequency of combination of bread and butter from sales transactions

Find out the frequency of repetition of dimension value combinations – e.g. frequency of combination of  bread and butter from sales transactions.                                                                                                                                                                

Scenario:

The data has date wise order details such as Order Id, Products and Order amount.

 

Requirement:

Find out the maximum repeated combination of dimension values from the data.

(e.g. Milk and bread are sold together maximum times).

 

Sample Data

The sample data is also attached herewith.

Step:1 

1.Create a copy (Dt_Cross_Sales_B ) of the main dataset (Dt_Cross_Sales_A) to find out the combination count between the data. 

2.Create a custom column called count with value as 1 for getting the total repeated count.

  a. Right click on the cell and click on Add Column -> Custom.

                            Create a Custom Column

                                Custom Column expression

Step: 2

Use Self Join to get Product A against Product B.

1.Add the copied dataset - Dt_Cross_Sales_B  to the main dataset - Dt_CrossSales_A

2.Apply blend JOIN and select the Dt_Cross_Sales_B dataset.

3.Use Inner JOIN based on Order ID = Order ID, Product_A != Product_B  (for not getting the same product in the combination) and Date = Date.

                                                        Self join condition

The resultant dataset will have all the products against their combination products as below.

                                                                                    Result after Join

The output is given below in a crosstab with products and their combination products with their repetition count.

In the crosstab, take Product_A in the row and the Product_B in the column with Count as the data.

                                                                Product count against different products

The output is given below in a ‘Heat map’ graph with products and their combination products with their repetition count.

In the graph also, take Product_A in the row and the Product_B in the column with Count as the data.

                                                                            Result plotted in a heat map

Note: This article is based on Smarten Version 5.0. This may or may not be relevant to the Smarten version you may be using.

blend Dataset Join SSDP CrossSell Dataset join