Merge actual data in financial year, with Plan data in calendar year, and create plan vs actual data as per financial year?

Scenario:

Projection – Plan sales data is in Calendar year, monthly data.

Actual sales data is in financial year, monthly data.

Requirement:

Merge actual data in financial year, with Plan data in calendar year, and create plan vs actual data as per financial year

Sample data (Actual data and Plan data) is attached herewith.

Learning from the exercise

Approach 1

To achieve this, we need to first convert Calendar quarter to Financial quarter in the Projection dataset. Then Join with the Financial Year dataset (Actual) to create the final dataset.

1) Split the calendar quarter column from right using Split functionality.
For eg: Split Q1, Q2, Q3, Q4 to get 1,2,3,4.
a. Right click on the column cell- Split -> Split to column.
b. Split by Length = 1
c. Split from Right
d. Specify the new column name (Financial_Quarter_Projection) and Click Apply.

2) Subtract the new FinancialQuarterProjection value by 1.
a. Right click on FinancialQuarterProjection - > Transform - > More
b. Choose minus operation and give ‘Argument 2’ as static value ‘1’ -> Click Apply.

3) Get the mod of the Quarter by 4.
a. Right click on FinancialQuarterProjection - Transform - More
b. Choose mod operation and provide the ‘Argument 2’ = 4 - > Click Apply.

4) Replace values ‘0’ with ‘4’ in FinancialQuarterProjection column.
a. Right click on the FinancialQuarterProjection column - > Find & Replace
b. Find value = 0, Replace value = 4 -> Click Apply

5) JOIN the ‘CalendarYear' and ‘FinancialQuarterProjection' column (of Planned dataset) with the Year and Quarter of Actual dataset.

The final dataset (Planned vs Actual) will be displayed as per Financial year.

Approach 2

In Projection dataset, create Financial month column from the Calendar quarters. Then Join with FinancialYear and FinancialMonth (of Actual dataset) to get the final dataset.

 1. To achieve this, create a new Financial Month column.

a. Right click on any cell and Add column -> Custom.
b. Provide column name as ‘FinancialMonth’ and Expression as “ifCase( CalendarQuarter == "Q1", "04", ifCase(CalendarQuarter == "Q2", "07", ifCase(CalendarQuarter == "Q3", "10", "01") ) )”.
b. Click Apply.

3. Join the ‘FinanciaYear' and ‘FinancialMonth' column (of Planned dataset) with the the Year and Month of Actual dataset.

The final dataset (Planned vs Actual) will be displayed as per Financial year.

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.