How to derive the MTD achievement based on monthly target and daily sales data on various dimensions

Modified on Wed, 6 Mar at 4:54 PM

Scenario:

The granularity of target data is monthly and sales data is daily.

 

Requirement:

Derive the MTD achievement based on monthly target and daily sales data on various dimensions.

 

Sample Data:

Sample data is attached herewith.

 

1. The daily sales data


Below given is the daily sales data, employee wise, product category wise granular data. There can be multiple sales entries for the same product for a single day.

  


 

2. Monthly Company wide Target


 Monthly target for the company has been given as below.

 

 

 

3. Monthly Employee wise Target

 

Employee wise monthly target has been defined as below.

 

 4. Monthly Product category wise Target


 Product category wise monthly target for different employees has been defined as below.

 

 

As per the scenario, The TARGET data holds target sales on a month level and The SALES data holds sales by day.The connection would be a one-to-many relationship from the target data to the sales data as shown below.


                                     EXAMPLE: EMPLOYEE MONTHLY TARGET AND DAILY SALES DATA


To combine daily sales data and monthly target data in one resultset together, the target data should be mapped and displayed for daily sales. There can be multiple entries of sales for the same day. In such cases, the target should be repeated for all the entries.

As per the above example, we need to have target ‘2000’ for ‘Jonathan Doherty’ – ‘August’ month repeated as below in the resultant data.

                                                                    THE RESULTANT DATA


 

Approach 1


Using JOIN:

 

1. JOIN the daily sales dataset with the other target datasets (Company wise target, Employee wise target, Product category wise target) to bring in the target data to the final dataset.

Use JOIN conditions as below.


 a. LEFT OUTER JOIN with Company wise monthly target

Company = Company, Date_Month = Month, Date_Year = Year

 

b. LEFT OUTER JOIN with Employee wise monthly target

Employee = Employee, Date_Month = Month, Date_Year = Year

 

c. LEFT OUTER JOIN with Product Category wise monthly target

Employee = Employee, ProductCategory = ProductCategory, Date_Month = Month, Date_Year = Year

 

 

FINAL DATASET WITH SALES AND TARGET DATA

 

The final dataset will have Monthly Company target, Monthly Employee target, Monthly Product Category target with daily sales data as below. Here all the monthly targets will be repeated against the daily sales data. There can be multiple sales entries for the same product or employee in a day. In such cases too, the monthly target will be repeated for all daily transactions. 


Result – Monthly Company-Employee-Product Category wise Achievement

 

The below crosstab shows Monthly Employee, Product Category and Company wise Achievement at one place. Since the aggregation level of sales and target is different for various dimensions, we need to use different operations at data level and summary level.


Data level: 

1. Sales will display the aggregated sum of sales for all the days – day wise sum 

2. To get the Monthly CompTarget, use the Average data operation in the data.–monthwise average 

3. To display the MonthlyEmpTarget, use the Last data operation – monthwise last target entry.

4. For MonthlyEmpProdTarget, use Average operation – monthwise average.

 

Summary Level:

1. For company level Summary, use Group Sum for calculating the total of MonthlyCompTarget, MonthlyEmpTarget,     MonthlyEmpProdTarget

2. For Month level Summary, to get the total of Sales, MonthlyEmpTarget, MonthlyEmpProdTarget use Group Sum operation. For MonthlyCompTarget total, use Average operation.

3. For Employee level Summary, use Default Summary operation for getting the total of Sales and MonthlyCompTarget. For MonthlyEmpTarget and MonthlyEmpProdTarget, use GroupSum operation.

 

CROSSTAB WITH SALES, TARGETS AND ACHIEVEMENTS


Approach 2

 

Using UNION with Augmented columns:

In UNION option, the target datasets and sales datasets are to be prepared by adding NULL value columns for Sales and Targets respectively. Ie; Add NULL Target columns in the Sales dataset and NULL Sales columns in the Target dataset.

For example; The below Sales dataset is made with a column called “MonthlyEmpTarget” with NULL values to Blend with Monthly Employee Target Dataset that has “Sales” column with NULL values.


DAILY SALES DATASET WITHOUT TARGET

 

To add NULL values;

a) Right click on any value Add Column -> Custom

b) Provide name as “MonthlyEmpTarget” and expression as “NULL” and Click APPLY.

 

                                                    ADDING THE TARGET COLUMN

 

c) When the column is created, Transform the datatype to INT.

 


DAILY SALES DATASET WITH EMPLOYEE TARGET COLUMN

 

Similarly add ‘Sales’ column with NULL values in Employee Target dataset.



EMPLOYEE TARGET DATASET WITH SALES COLUMN

 

After adding the columns, use BLEND UNION option to create the final dataset with Sales and Target column together. The sales column and target column will show the aggregated data while calculating the achievement in a crosstab.

FINAL DATASET AFTER UNION, WITH SALES AND TARGET DATA


Result – Monthly Employee wise Sales and Target

 

The below crosstab shows Monthly Employee wise Sales Target at one place. Since the Target and Sales are grouped and aggregated, there is no need to change the data operations at data level and summary level here.

 

Data level: 

1. Sales will display the aggregated sum of sales for all the days – day wise sum 

2. ‘EmployewiseMonthlyTarget’ will also aggregate the target values and display the sum of target – month wise target sum.

 

Summary Level:

1. For year wise Summary, use ‘Default’ summary for calculating the sum of the sales and MonthlyEmpTarget.

2. For month wise Summary also, use ‘Default’ summary for calculating the sum of the sales and MonthlyEmpTarget.

  

MONTHLY EMPLOYEE WISE SALES AND TARGET

 

 




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

blend MTD monthly target daily sales

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