7 Replies Latest reply: Jul 13, 2011 3:28 PM by AndreBer RSS

    Rolling months in script

    beciafreestyle

      Dear QlikView users,

      I have a problem with Rolling Months option in my script. Thanks to Michael Steedle I could use it in one file, but it seems that it doesn't work in my new model. My Main Calendar looks like that:

      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,
      YearToDate(TempDate, 0, Ceil(Month(Today())/3) * 3 - 2 ) as CurQTDFlag,
      YearToDate(TempDate, -1, Ceil(Month(Today())/3) * 3 - 2 ) as LastQTDFlag,
      Year2Date(TempDate, 0, Month(Today())) as CurMTDFlag,
      Year2Date(TempDate, -1, Month(Today())) as LastMTDFlag

      RESIDENT TempCalendar
      ORDER BY TempDate ASC;

      DROP TABLE TempCalendar;

      Could anyone help?

      Thank you in advance,

      Beata Jablonska

      P.S. I would forget! Does anyone have an idea why values for my CurMTDFlag are negative(ex. sum of sales turns -60.00 Euro, instead of 60.000 Euro)? CurYTDFlag works correctly.

        • Rolling months in script
          Miguel Angel Baeyens de Arce

          Hello Beata,

          To your MasterCalendar, I would try with functions InQuarterToDate and InMonthToDate as flags to know your rolling dates. Take into account that the result of all these year to date functions is "true" or "false". QlikView speaking, true equals to "-1" and false equals to "0", so you should not add to your CurYTD and LastYTD Flags, instead of setting them in the set analysis

          Sum({< LastYTDFlag = {'-1'} >} Sales)
          will sum sales for the last year rolling period.

            • Rolling months in script
              beciafreestyle

              Dear Miguel,

               

              Now I understand why my values where on negative, thanks! Another question- any idea how I can make Rolling Months option? I would like to ahow on bar chart Moving Annual Total (ex. for February 2010 there will be shown sum of last 12 months sales etc).

               

              Thank you in advance,

              Best greetings,

              Beata Jablonska

                • Rolling months in script
                  Miguel Angel Baeyens de Arce

                  Hello Beata,

                  As of today, February 2, with

                  {< CalendarDate = {'>=$(=AddMonths(Today(), -12))<=$(=Date(Today()))'} >}
                  as set analysis, will sum from Feb 2 2009 to Feb 2 2010. Is that what you are looking for?

                    • Rolling months in script
                      beciafreestyle

                      Dear Miguel,

                       

                      Yes, it sound like that what I need. Right now I am achieving MAT by usage of simple accumulaqtion (12 steps back), but it has disadvantage of showing all of the months in your system (also those that cannot accumalute 12 steps back) and with this I cannot count % MAT (unless you maybe know how to check the difference between bars, current and from previous month?). Below you can see my MAT (at least smth like MAT)

                      When I tried to use your expression in bart chart, it says 'no data to display'. I faced this problem with some other functions earlier, that's why I though the most convinient would be to use RollingMonths in script.

                      Thank you for your help,

                      Beata

                        • Rolling months in script
                          Miguel Angel Baeyens de Arce

                          Hello Beata,

                          Having Month and Year as dimensions and sum of values as expression, your should modify the above set analysis changing "CalendarDate" for your master fact date (i.e.: InvoiceDate). You should get results provided you have your master calendar working properly and invoices which dates go from today minus12 months to today.

                          Or I may be missing something...

                            • Rolling months in script
                              beciafreestyle

                              Dear Miguel,

                              Hmmmm yes, there has to be smth wrong, cause now I tried also:

                              sum({<CalendarDate={">$(=addmonths(Date,-12)) <=$(=Date)"}>} InvoiceAmount)

                              and still it says "no data to display". The dates of my invoices are linked to Master Calendar, so I have date field "CalendarDate" only. Maybe that's the problem?

                              Anyway, I see, that maybe I didn't explain my problem properly. I want to show last 12 months, but each bar should sum previous 12 months. To show only 12 months, I use:

                              sum(if(CalendarDate>addmonths(today(),-12),Sales))

                              and first requirment is fullfilled (it's only 12 months on graph), but now I want each bar to show sum of sales for last 12 months (so called Moving Annual Total) and here I'm stucked...

                              Thank you for your help and patience,

                               

                              Greetings,

                              Beata