DOES ANYONE KNOW HOW TO CALCULATE YTD AND MAT?

Tue, 02 Feb 2010 10:29:02 GMT

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

DOES ANYONE KNOW HOW TO CALCULATE YTD AND MAT?

Tue, 02 Feb 2010 11:49:44 GMT

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!

topic DOES ANYONE KNOW HOW TO CALCULATE YTD AND MAT? in QlikView

DOES ANYONE KNOW HOW TO CALCULATE YTD AND MAT?

DOES ANYONE KNOW HOW TO CALCULATE YTD AND MAT?