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

Modified on Mon, 12 Oct 2020 at 08:49 PM

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.


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 atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article