Merge monthly plan data with actual daily sales data and create plan vs actual data

Scenario:

The actual sales data contains daily Sales person wise transaction data.

The plan data has monthly target by sales person.

Requirement:

We need monthly plan vs actual data by sales person.

Sample Data

(Sample Daily transaction and Monthly Target data is attached herewith.)

Daily Sales transaction

Monthly Target data

Learning from the exercise

Approach 1:

Step 1: 

In Daily Transaction dataset, we need to get the month wise aggregated gross sales as the Target given is monthly. 

1. Right on the date column ->  Transform -> First date of the month

                            First date of the month

Step 2: 

Perform Sum Aggregation operation on Gross sales to get the monthly aggregated sales. (in Daily Transaction dataset)

1.Click on Aggregate option in the toolbar.

2.Choose SUM aggregation for GrossSales measure and click APPLY.

Sum Aggregation

Step 3: 

We need to join the Daily Transaction dataset with Monthly Target dataset to bring the target.

1. Click on Add Dataset(s).

2. Click on Blend-JOIN, and choose the JOIN conditions as 

EmployeeName = EmployeeName and Date = Date.

                                                                        Inner Join

The final dataset will have monthly sales and target together.

Merged Sales and Target data

Approach 2:

Step 1: 

In Daily Transaction dataset and Monthly Target dataset, the Year and Month column need to be generated to perform the JOIN operation.

1.Right click on Date column -> Add Column -> Year

Similarly generate Month column.

                Add Year and Month column

Step 2: 

We need to join the Daily Transaction dataset with Monthly Target dataset to bring the target.

1. Click on Add Dataset(s).

2. Click on Blend-JOIN, and choose the JOIN conditions as 

EmployeeName = EmployeeName, Month = Month and Year = Year.

                                                        Inner Join

The final dataset will have monthly sales and target together.

Monthly sales and Target data

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.