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

Modified on Tue, 3 Nov, 2020 at 8:58 PM

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

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article