Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

MOM

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

4 Replies
vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

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?

vinieme12
Champion III
Champion III

Yes, if you want to compare lastyear Dec vs Current Year Jan Stick with using Addmonths

Check the attached sample

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
HirisH_V7
Master
Master

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,Month on Month Comparision-241029 Variables.PNG

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

HirisH
“Aspire to Inspire before we Expire!”