cancel
Showing results for 
Search instead for 
Did you mean: 

Challenge #3 Month over Month Growth Rate

Rasha
Employee
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:
Screen Shot 2022-12-21 at 2.22.13 PM.png

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

LukeG
Employee
Employee

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

https://www.youtube.com/watch?v=Xj6FpwT4fp8

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 = ...

zhong
Partner
Partner

My result: 

challenge#3.png

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;

 

 

StevenPeng
Partner
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
 
StevenPeng_0-1680290563170.png