cancel
Showing results for
Did you mean:

## Challenge #3 Month over Month Growth Rate

Employee

As a Sales Analyst, I need your help to know how my company's revenue grows throughout a period using the month-over-month growth formula :

(Month growth rate = (This month - Last month) / Last month)

I am looking for this insight:

You can use the attached Sales data and Date lookup data.

I know it is a little bit tricky, But  When you try it you will find it is fun 🙂

7 REPLIES 7
Employee

Here is a video of a code-friendly solution! The script for the MV using PySpark is in the description. Hope you enjoy!

This video demonstrates how to solve the third challenge on the Incorta Community. Here is the pyspark code used to in the materialized view: # import libraries from pyspark.sql.functions import sum, lag from pyspark .sql.window import Window # read table as spark dataframe df = ...
Partner

My result:

In order to compare revenue month by month, we will need to find the previous revenue for the current month. I find it is easy to solve this by leveraging SQL Query. We can do so by creating either an Incorta SQL table or Incorta Materialized View.

My solution:

1. upload all files, create schema and add tables to the scheme

2. Add materialized view (Month_over_Month_Revenue), select language as Spark SQL. (see following code)

3. Create Join between Date_US and Month_over_Month_Revenue on CALENDAR_MONTH_NUMBER = MONTH_NUMBER (This helps us show Month Name in the insight)

4. Create insight, choose Aggregated table.

1. drag CALENDAR_YEAR (from Month_over_Month_Revenue), Month_Name (from Date_US) to grouping dimension. Select Month_Name and add Month_Number (from Date_US) into sort by in Ascending order
2. drag Current_Revenue, Previous_Month_Revenue (from Month_over_Month_Revenue) to measure.
3. add formula to measure, enter (Current_Revenue - Previous_Month_Revenue) / Previous_Month_Revenue, name this column as Growth. Create two conditions (if Growth > 0 and if Growth <= 0), choose a different background color for each.

WITH cur as (
SELECT
CALENDAR_YEAR as CALENDAR_YEAR,
CALENDAR_MONTH_NUMBER as CALENDAR_MONTH_NUMBER,
SUM(AMOUNT_SOLD) as AMOUNT_SOLD
FROM
sch_CommunityChallenge3.SALES__1_
GROUP BY
CALENDAR_YEAR,
CALENDAR_MONTH_NUMBER
)
SELECT
CALENDAR_YEAR,
CALENDAR_MONTH_NUMBER,
AMOUNT_SOLD as Current_Revenue,
LAG(AMOUNT_SOLD, 1) OVER (
ORDER BY
CALENDAR_YEAR,
CALENDAR_MONTH_NUMBER
) as Previous_Month_Revenue
FROM
cur;

Partner

My solution doesn't need any SQL code or new tables.

1. Add a formula column Month_Start_Date to sales table:

monthStartDate(
sch_CommunityChallenge3.SALES__1_.TIME_ID
)

2. Go to insight, select aggregated table, group by Month_Start_Date, previous month revenue can be calculated with Incorta formula:
sum(
sch_CommunityChallenge3.SALES__1_.AMOUNT_SOLD,
ago(
sch_CommunityChallenge3.SALES__1_.Month_Start_Date,
1,
'month'
)
)
3. Calculate growth over month based on last step