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 🙂
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.
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;
My solution doesn't need any SQL code or new tables.
1. Add a formula column Month_Start_Date to sales table: