Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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?
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
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.
i believe you are still using a date field, can you post a sample app or some screenshots!
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 🙂
Solved.
I put =num(Day(Close_Date)) on column1 and I already have what I needed.
Thanks!!