1 Reply Latest reply: Feb 2, 2010 6:49 AM by beciafreestyle RSS

    DOES ANYONE KNOW HOW TO CALCULATE YTD AND MAT?

    salvatore80

      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?
          beciafreestyle

          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!