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: 
Not applicable

DOES ANYONE KNOW HOW TO CALCULATE YTD AND MAT?

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

1 Reply
Not applicable
Author

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!