6 Replies Latest reply: Jul 28, 2016 9:32 AM by Vincent Den Heten RSS

    Rolling Weeks Question

    Steve Margerin

      I am trying to create a KPI that Sums the last four weeks of data.  I have read and tried the numerous solutions on creating Rolling 'N' expressions in the posts to no joy. I suspect either my data is formatted wrong or I am missing something. I am fairly new to Qlik.

       

      The application is to show three activity metrics for salespeople as a rolling four week total (thus a month rolling weekly). below a snapshot of my data (in excel)

       

                

      RepFiscalYearMonthMonth-YearWeekNumWeek Beginning30DayPipe90DayPipeContactAddsFirstinsNewOps
      RachelFY15AprApr-201514/6/15$441,600$797,600000
      RachelFY15AprApr-201524/13/15$410,500$862,600000
      RachelFY15AprApr-201534/20/15$410,500$862,600000
      RachelFY15AprApr-201544/27/15$387,000$689,100051
      RachelFY15MayMay-201555/4/15$318,600$659,100000
      RachelFY15MayMay-201565/11/15$305,000$645,500001
      RachelFY15MayMay-201575/18/15$267,000$599,500001
      RachelFY15MayMay-201585/25/15$388,000$707,500052
      RachelFY15JunJun-201596/1/15$314,300$840,550000
      RachelFY15JunJun-2015106/8/15$314,300$905,050000
      RachelFY15JunJun-2015116/15/15$314,300$895,050000
      RachelFY15JunJun-2015126/22/15$322,300$986,050001
      RachelFY15JulJul-2015136/29/15$321,300$978,050001
      RachelFY15JulJul-2015147/6/15$257,800$995,050000
      RachelFY15JulJul-2015157/13/15$282,800$1,020,0500101
      RachelFY15JulJul-2015167/20/15$295,050$1,020,0500101
      RachelFY15JulJul-2015177/27/15$360,050$1,028,0500101
      RachelFY15AugAug-2015188/3/15$259,300$929,800000
      RachelFY15AugAug-2015198/10/15$259,300$937,988000
      RachelFY15AugAug-2015208/17/15$259,300$937,988000
      RachelFY15AugAug-2015218/24/15$282,300$877,988000
      RachelFY15AugAug-2015228/31/15$282,300$404,688000
      RachelFY15SepSep-2015239/7/15$261,500$653,188200
      RachelFY15SepSep-2015249/14/15$261,500$643,188400
      RachelFY15SepSep-2015259/21/15$261,500$657,188342
      RachelFY15SepSep-2015269/28/15$29,500$504,188533
      RachelFY15OctOct-20152710/5/15$130,000$476,688040
      RachelFY15OctOct-20152810/12/15
      RachelFY15OctOct-20152910/19/15
      RachelFY15OctOct-20153010/26/15
      RachelFY15NovNov-20153111/2/15

       

       

      So, for example, the KPI for "FirstIn's" should result in 11 and "New Opps" should result in 5. There is one tab per Sales Rep. All the simple Sums and Averages seem to be working fine, I just can't get the rollings to work.

       

      I have tried rangesum(Above(Sum(Firstins),0,4)) to no avail. The result I get is 51- which is the sum of the entire column.

       

      Any help is much appreciated. I have tried to figure this out myself for too long.

        • Re: Rolling Weeks Question
          Oleg Troyansky

          The solution based on the Above() function, even if you manage to get it to work, will only be relevant in a table where data is listed at the detailed level, weekly. Imagine that you need the same kind of information in a free-standing KPI calculation, as a total (or average) of all sales people.

           

          A more universal solution is to use the so called "As of Date" table. There are multiple references on this site that explain how to build and use the "As of Date" table. You can also find it in my blog article here:

           

          QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

           

          I also describe it in more detail in my new book QlikView Your Business (it also applies to Qlik Sense).

           

          cheers,

          Oleg Troyansky

          QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

            • Re: Rolling Weeks Question
              Steve Margerin

              Thanks for the quick reply Oleg.

               

              In reviewing your solution on the blog, it makes sense and may be the solution to my issue. Can you quickly apply it to my issue of rolling weeks? In your load script, can I just substitute Week/s for the references to Month/s?

               

              Also a real newbie question- just to check, I add the script to the data loader, right?

                • Re: Rolling Weeks Question
                  Oleg Troyansky

                  Hi Steve,

                   

                  sorry for the delay - our time difference doesn't help :-)

                   

                  With weeks, the math is simpler. While AddMonths() helps us overcome the different lengths of different months, weeks are conveniently sized for us at 7 days each. So, the condition for the current week would be:

                   

                  If( Date1> Date2-7, 1, 0) as _ThisWeek_Flag.

                   

                  Or, if you'd like to stick to the same Week Start date, you could do something like this:

                   

                  If( WeekStart(Date1) = WeekStart(Date2), 1, 0) as _ThisWeek_Flag

                   

                  Or, to calculate the same for the "Last 12 weeks" condition:

                   

                  If( WeekStart(Date1) >= WeekStart(Date2 - 12*7), 1, 0) as _Last12Weeks_Flag

                   

                  And - yes, this logic belongs in the Data Load Script. We typically add it when we create the Master Calendar table. If you don't have one, simply add it to the load statement where your Date field is loaded.

                   

                  cheers,

                  Oleg Troyansky

                    • Re: Rolling Weeks Question
                      Steve Margerin

                      Oleg- Sorry for taking so long to get back. My projects shifted for a few days. It appears that your solution is exactly what I needed in the first place. I can't seem to get it to work though. I believe its a newbie thing as my script errors out at the "Date1". Are the 'Date1' and 'Date2' fields supposed to be a placeholder for something else?

                       

                      My calendar script is below:

                       

                      //Generate calendar range based on the Claendar start and end variables. All date within the range are genrated.

                       

                       

                      LET vCalendarStart = Date#('2015-01-01'); // Static start

                      LET vCalendarEnd = YearEnd(today()); // End of current year

                       

                       

                      LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;

                       

                       

                      Calendar:

                      LOAD

                        *,

                        If(Date>=(Today()-30) AND Date<Today(), 1, 0) AS Is30DayPeriod,

                        If(Week=Week(Today()), 1, 0) AS IsCurrentWeek,

                        If(Date<=YearEnd(today()) AND Date>Today(), 1, 0) AS IsLeftCurrentYear

                      ;

                      LOAD

                        *,

                        Num(Ceil(Month/3), 'Q0') AS Quarter,

                        WeekDay(Date) AS WeekDay,

                        Date(MonthStart(Date), 'YYYY MMM') AS YearMonth,

                        WeekName(Date) AS YearWeek,

                        Date#(Date(Date, 'MMMM'), 'MMMM') AS MonthName //Month according to MonthLongNames definition

                      ;

                      LOAD

                        Date,

                        Year(Date) AS Year,

                        Num(Month(Date), '00') AS Month,

                        Num(Day(Date), '00') AS Day,

                        Num(Week(Date), '00') AS Week

                        ;

                      LOAD

                        Date($(#vCalendarStart) + RecNo()-1) AS Date 

                      AutoGenerate $(#vCalendarLength);

                       

                       

                      // 1. Create a distinct list of Months:

                      TransactionMonths:

                      Load distinct

                        Month

                      Resident

                        Calendar

                      ;

                       

                      // 2. Create a distinct list of DisplayMonths:

                      DisplayMonths:

                      Load

                        Month as DisplayMonth

                      Resident

                        TransactionMonths

                      ;

                       

                      // 3. Create a list of all possible combinations:

                      join (TransactionMonths) load * resident DisplayMonths;

                      // 4. Reload the same table and calculate all the necessary flags:

                      MonthsLink:

                      Load

                      Month,

                      DisplayMonth,

                      IF( Month >= AddMonths(DisplayMonth, -6) , 1, 0) as Last6MonthsFlag,

                      IF( Month >= AddMonths(DisplayMonth, -12) , 1, 0) as Last12MonthsFlag,

                      IF( Month = DisplayMonth , 1, 0) as SameMonthFlag

                      Resident

                      TransactionMonths

                      ;

                      drop table TransactionMonths;

                      //===================================

                       

                       

                       

                       

                      // 1. Create a distinct list of Weeks:

                      TransactionWeeks:

                      Load distinct

                      Week

                      Resident

                      Calendar

                      ;

                       

                      // 2. Create a distinct list of DisplayWeeks:

                      DisplayWeeks:

                      Load

                      Week as DisplayWeeks

                      Resident

                      TransactionWeeks

                      ;

                       

                      // 3. Create a list of all possible combinations:

                      join (TransactionWeeks) load * resident DisplayWeeks;

                       

                       

                      // 4. Reload the same table and calculate all the necessary flags:

                      WeeksLink:

                      Load

                        Week,

                        DisplayWeeks,

                        If( WeekStart(Date1) = WeekStart(Date2), 1, 0) as _ThisWeek_Flag,

                        If( WeekStart(Date1) >= WeekStart(Date2 - 12*7), 1, 0) as _Last12Weeks_Flag

                      Resident

                        TransactionWeeks

                      ;

                      drop table TransactionWeeks;

                      //===================================

                        • Re: Rolling Weeks Question
                          Oleg Troyansky

                          Hi Steve,

                           

                          yes, Date1 and Date2 were placeholders for the transaction date and the display date. I just used them to give you an example of the logic.

                          • Re: Rolling Weeks Question
                            Vincent Den Heten

                            Hi Steve,

                             

                            How did you and Oleg fixed this? I tried copying your script and adding my own performancedate but it says it can't find a date.:

                             

                            // 4. Reload the same table and calculate all the necessary flags:

                            WeeksLink:

                            Load

                              Week,

                              DisplayWeeks,

                            //  If( WeekStart(Date) = WeekStart(Date), 1, 0) as _ThisWeek_Flag,

                              If( WeekStart(Date) >= WeekStart(Date - 12*7), 1, 0) as _Last12Weeks_Flag

                            Resident

                              TransactionWeeks