12-21-2022 04:36 AM
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 🙂
03-09-2023 07:02 PM
Here is a video of a code-friendly solution! The script for the MV using PySpark is in the description. Hope you enjoy!
03-27-2023 11:00 AM
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.
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;
03-31-2023 12:24 PM
My solution doesn't need any SQL code or new tables.
1. Add a formula column Month_Start_Date to sales table: