DOES ANYONE KNOW HOW TO CALCULATE YTD AND MAT?

[#1]

Dear all, I just kept in touch with QW. I'm struggling in calculating YTD and MAT. I have very simple data source, classified as follow: Class, Product, and Sales splitted over the last three years. I already built up a PIVOT table, and I need to calculate YTD and MAT. I tried with different expressions but they don't work at all. Please does anyone know how to calculate Them? I attached the file where I'm working on...

Thanks in advance for your help

PS. I have QW personal edition, please if you modify the attached file, make it downloadable for me...thank you again

[#2]

Dear Salvatore,

To calculate YTD it's the best to mkae Master Calendar in your script, and thenafter use flags. Here you can see mine example, that's working in personal eition 9:

LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 1)));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo()-1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:
LOAD
TempDate AS CalendarDate,
Day(TempDate) AS CalendarDay,
Week(TempDate) AS CalendarWeek,
Weekday(TempDate) AS WeekDay,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
Week (TempDate) & '-'& Year (TempDate) as CalendarWeekAndYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag


RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

After you will add this tab to your script it's very easy, you use simple expressions, ex:

Sum(CurYTDFlag*Sales)

With movinga annual totat thing is more complicated. I am searching for sollution for one month already, but till now no full success. There are two ways to do smth like MAT:

a) try to find on forum posts about Rolling Months

b) use the bar chart, with simple expression : Sum(Sales) and in the expressions tab, click the option 'Accumulate' and choose 12 previous months. Unfortunate;y in this case it will show data for all months you have, instead of last 12 only. Acha and don't forget to use proper dimension, in my case it's =makedate(CalendarYear,CalendarMonth) - so it shows all months in given yers.

Good luck, if you will know something more about MAT, let me know!