2 Replies Latest reply: Apr 7, 2014 8:49 AM by Erica Whalley RSS

    Negative Weeks

    Isabel Nowinowski

      Hello,

              I was wondering how I go about subtracting weeks so that I do not result in a negative week. For example, if it is currently week 12 and I am subtracting 13 weeks, I do not get a value.

       

      I have tried to determine the POS Net Sales for 13 weeks from the Current week using Set Analysis:

       

      SUM({$<Year={$(=Max(Year))},

        Week=($(=(vCurrentWeek)-13))},

      SKU=P(SKU)>} [POS Net Units])


      However, I need to somehow incorporate the following if statement:

      if($(=(vCurrentWeek)-13)<0, Week=$(=(vCurrentWeek)-13)+52)



      Any help would be greatly appreciated! I am trying to make it uniform so that if I subtract say 14, or 15 weeks, it will also work.

        • Re: Negative Weeks
          Gysbert Wassenaar

          The usual solution is to create a serial week field in the script and use that field instead. By serial week field I mean a field with values where every weeks value is 1 larger than the previous weeks value. Something like autonumber(Year*100+Week,'_WeekCounter') as _WeekSerial. If you have a date field you can use the Qlikview Components script library to create a Calendar table from that date field and you'll get the _WeekSerial field in that table too.

          • Re: Negative Weeks

            Hi Isabel

             

            You could try using the mod(int1,int2) function in your formulae. It returns the modulus of two integers, ie the remainder when you divide int2 by int1. It also works for negative numbers:

             

             

            =

            mod(-2,52)

             

             

            returns "50"

             

            Erica