topic Re: MOM in QlikView

MOM

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: MOM

vinieme12 — Fri, 25 Nov 2016 04:54:25 GMT

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)

Re: MOM

Anonymous — Fri, 25 Nov 2016 05:11:01 GMT

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?

Re: MOM

vinieme12 — Fri, 25 Nov 2016 06:01:20 GMT

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

Check the attached sample

Re: MOM

HirisH_V7 — Sun, 27 Nov 2016 08:02:29 GMT

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