Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
May I get help on calculate month over month growth rate?
LOAD sku_id,
year, (year is int, e.g. 2015; 2016)
month, (month is also int from 1 to 12)
date(makedate(year,month),'MMM-YY') as "month_year",
profit
from XXXXXX
I used
Sum({$<[month_year]={">=$(=Date(AddMonths(Max(month_year), -1), 'MMM-YYYY')<=$(=Date(Max(month_year), 'MMM-YYYY'))"}>} profit) to calculate MOM, but it is wrong.
Welcome any idea.
Thanks and best regards,
Jerry
in script create a yearmonth field as below
LOAD sku_id,
year, (year is int, e.g. 2015; 2016)
month, (month is also int from 1 to 12)
year&NUM#(month,'##') as YearMonth // (201601,201602 format)
date(makedate(year,month),'MMM-YY') as "month_year",
profit
from XXXXXX
by MoM what do you mean? YTD?
SUM(
{<
YearMonth = {">=$(=max(YearMonth)-1)<=$(=max(YearMonth))"}
>}
Profit)
Thanks Vineeth quick response.
I want to show the profit for current month and previous month, can the calculate the MOM growth rate for current month using (Current profit-previous profit)/previous profit.
For your solution, I find the profit value is same as current month profit. Is there any bug if current month=1?
Yes, if you want to compare lastyear Dec vs Current Year Jan Stick with using Addmonths
Check the attached sample
Hi Check this,
By using the sample data,
Data:
LOAD Customer,
[Sales Order ID],
ShipDate as Date,
year(ShipDate) as Year,
Num(Month(ShipDate)) as MonthNum,
Month(ShipDate) as Month,
Product,
Sales,
Quantity
FROM
[Sales Orders.xls]
(biff, embedded labels, table is [Sales Orders$]);
And Then by creating a variable for picking the max Year and MonthNum,
Then in the straight table using the following expressions for Previous Month and current Month Comparisons,
Previous Month
=If(vMaxMonth=1,Sum({<Year={$(=vMaxYear-1)},MonthNum={12}>}Sales),
Sum({<Year={$(=vMaxYear)},MonthNum={$(=vMaxMonth-1)}>}Sales)
)
Current Month
=Sum({<Year={$(=vMaxYear)},MonthNum={$(=vMaxMonth)}>}Sales)
This will help in comparing the Month on Month based on your data, you can pass your data flag into the vMaxMonth variable to pick the maxmonth for that particular data set.
Hope this Helps,
PFA,
Hirish