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

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.