MoM and YoY

[Shaglok]

Hello.

I am facing a lot a problems trying to implement MoM and YoY measures. I checked a lot of posts, but I think that my calendar is a bit diferent. Here it is:

QuartersMap:
MAPPING
LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Dates:
LOAD
min(Close_Date) as minDate,
date(today(),'YYYY-MM-DD') as maxDate
resident [SALES];

LET vMinDate = Num(Peek('minDate',0,'Dates'));
LET vMaxDate = Num(Peek('maxDate',0,'Dates'));

DROP Table Dates;

TempCalendar:
LOAD
$(vMinDate) + Iterno()-1 As Num,
Date($(vMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar_Charge_Date:
Load
TempDate AS Close_Date,
week(TempDate) As Master_Week_CD,
Year(TempDate) As Master_Year_CD,
Year(TempDate)& '-W' &Week(TempDate) As Master_YearWeek_CD,
Month(TempDate) As Master_Month_CD,
Date(Floor(TempDate)) As Master_Day_CD,
YeartoDate(TempDate)*-1 as Master_CurYTDFlag_CD,
YeartoDate(TempDate,-1)*-1 as Master_LastYTDFlag_CD,
inyear(TempDate, Monthstart($(vMaxDate)),-1) as Master_RC12_CD,
Year(TempDate)& '-' &num(Month(TempDate)) As Master_YearMonth_CD,
ApplyMap('QuartersMap', month(TempDate), Null()) as Master_Quarter_CD,
Year(TempDate)& '-' &ApplyMap('QuartersMap', month(TempDate), Null()) as Master_YearQuarter_CD,
WeekDay(TempDate) as Master_WeekDay_CD
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;

According to that calendar, how can I easily start to use MoM calculations?

Thanks 

[ogster1974]

1. modify your master calendar to dual your Year month field so you have a numeric value.

2. add the following set analysis to your measures for Month on Month

{<Master_YearMonth_CD={"=Max(Master_YearMonth_CD)"}>} // current Year Month

{<Master_YearMonth_CD={"=Max(Master_YearMonth_CD)-1"}>} // previous Year Month

[vinieme12]

YoY

=sum({<Master_CurYTDFlag_CD={1}>}Measure) / sum({<Master_LastYTDFlag_CD={1}>}Measure)

OR

=sum({<Close_Date={">=$(=YearStart(Max(Close_Date)))<=$(=Max(Close_Date))"}>}Measure) / sum({<Close_Date={">=$(=YearStart(Max(Close_Date),-1))<=$(=AddYears(Max(Close_Date),-1))"}>}Measure)

MoM

=sum({<Close_Date={">=$(=MonthStart(Max(Close_Date)))<=$(=Max(Close_Date))"}>}Measure) / sum({<Close_Date={">=$(=MonthStart(AddYears(Max(Close_Date),-1)))<=$(=AddYears(Max(Close_Date),-1))"}>}Measure)

Master_YearMonth_CD  is a text field

[Shaglok]

Thanks!! I did it your way and it seems to be working. I can now calculate the MoM day-over-day.

But I am facing a small issue. When I try to put both measures in the same graphic (Date, CurrentMonth, PrevMonth), for example, the second column is always a 0 because the first column changes the date. I only can see data of the first column.

How can I solve this?

[vinieme12]

If you want to have a daily comparison  create a new field in your datamodel as below, then use this field in chart

Day(Date)&'-'&Month(Date) as Day_Month 

[Shaglok]

Hello and thanks.

Sadly, even using that field as date, the second column returns only 0's. If I use the previous month in the first column, the date changes.

[vinieme12]

i believe you are still using a date field, can you post a sample app or some screenshots!

[Shaglok]

Yes, sure.

This is the table:

And the measures:

Column 1: Close_Date

Column 2: sum({<Close_Date={">=$(=MonthStart(Max(Close_Date)))<=$(=Max(CloseDate))"}>}Counter) 

Column 3: sum({<Close_Date={">=$(=MonthStart(AddMonths(Max(Close_Date),-1)))<=$(=AddMonths(Max(Close_Date),-1))"}>} Counter)

Thanks for your help and patience 🙂

[Shaglok]

Solved.

I put =num(Day(Close_Date)) on column1 and I already have what I needed.

Thanks!!