topic Re: Challenge #3 Month over Month Growth Rate in Incorta Conundrums

Challenge #3 Month over Month Growth Rate

Rasha — Wed, 21 Dec 2022 12:36:07 GMT

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 🙂

Re: Challenge #3 Month over Month Growth Rate

TCoomes — Tue, 07 Feb 2023 16:22:36 GMT

Hi Rasha,
My solution involved created a materialized view using the Spark SQL feature which created a table that showed , Year, Month, Mth Sum, and Previous Month Sum. I then loaded that Schema in and created a formula inside the insight to show the growth percentage. Im interested to see if you had the same solution. Ive attached my graphic below.

Re: Challenge #3 Month over Month Growth Rate

JoeM — Tue, 07 Feb 2023 16:41:05 GMT

Great stuff @TCoomes !

Re: Challenge #3 Month over Month Growth Rate

LukeG — Tue, 14 Feb 2023 19:50:19 GMT

Not the most elegant, but I got the end result! Here is my solution:

Create analyzer table named MONTHLY_REVENUE with 3 columns: Year, Month, Revenue
Create analyzer table named PREVIOUS_MONTHLY_REVENUE with 3 columns: Year, Month, Previous Month Revenue
Join Tables on Year and Month
Create visualization with the Year, Month, Revenue, Previous Month Revenue, and Growth Rate using the formula provided.

In step 2, you can represent the previous month revenue by adjusting the month by 1 year. After doing so, the sum of revenue will represent the previous month.

Excited to see some other solutions!

Re: Challenge #3 Month over Month Growth Rate

oabdelkarim — Mon, 20 Feb 2023 14:59:23 GMT

I asked ChatGPT to answer this question, here is his reply 😀:

To calculate the month-over-month growth rate in Incorta, you will need to perform the following steps: 1. Join the `sales` and `months` tables on the `TIME_ID` column in the `sales` table and the `Date` column in the `months` table. 2. Create a calculated column in the joined table to calculate the revenue for each month by multiplying the `QUANTITY_SOLD` by the `AMOUNT_SOLD`. 3. Create two calculated columns in the joined table to calculate the revenue for each month and the previous month. To do this, you can use the following steps: a. Use the `Window` function to create a sliding window over the rows in the joined table, ordered by the `Date` column in the `months` table. b. Use the `LAG` function to retrieve the revenue value for the previous month for each row in the joined table. c. Use an `IF` statement to handle the case where there is no previous month for the first row in the joined table. In this case, you can set the revenue for the previous month to 0. 4. Create a calculated column in the joined table to calculate the month-over-month growth rate using the following formula: `Month growth rate = (This month revenue - Last month revenue) / Last month revenue` 5. Group the data by the `Month_Name` column to see the revenue for each month. 6. Create a visualization such as a line chart or bar chart to display the month-over-month growth rate. This is a more detailed outline of the steps needed to calculate the month-over-month growth rate in Incorta, including the exact formula that will be used. The specific details of the calculation will depend on the data and the requirements of your project.

Then asked him for more details on Step 3, and here it is:

To calculate the revenue for each month and the previous month in Incorta, you can use the following formula in a calculated column: ``` CASE WHEN ROW_NUMBER() OVER (ORDER BY "Date") = 1 THEN 0 ELSE LAG("Revenue", 1, 0) OVER (ORDER BY "Date") END ``` In this formula, the `ROW_NUMBER()` function is used to determine the row number for each row in the joined table, ordered by the `Date` column in the `months` table. The `LAG` function is used to retrieve the revenue value for the previous month for each row in the joined table. The `CASE` statement is used to handle the case where there is no previous month for the first row in the joined table. In this case, the revenue for the previous month is set to 0. This formula can be used to create two calculated columns in the joined table, one for the revenue for each month and one for the revenue for the previous month.

Re: Challenge #3 Month over Month Growth Rate

LukeG — Fri, 10 Mar 2023 03:02:50 GMT

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

Re: Challenge #3 Month over Month Growth Rate

zhong — Mon, 27 Mar 2023 18:00:28 GMT

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;

Re: Challenge #3 Month over Month Growth Rate

StevenPeng — Fri, 31 Mar 2023 19:24:43 GMT

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,

'month'

)

3. Calculate growth over month based on last step