.png "Employee"){kind=icon}
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 🙂
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 = ...
.svg "Partner"){kind=icon}
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.
- 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
- drag Current_Revenue, Previous_Month_Revenue (from Month_over_Month_Revenue) to measure.
- 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;
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
