4 Replies Latest reply: Jan 2, 2013 9:10 AM by Patric Lindblom RSS

    Need urgent Help!

    Patric Lindblom

      Hi

       

      I have a table called Time that is linked to other tables.
      It contains columns like YearWeek and YearWeekCounter.
      YearWeek starts at 200901 and ends at 201352, Current YearWeekCounter is at 210.
      How do I write an expression that is based on the current YearWeekCounter and then summarizing another's columnvalue(orders) 2 weeks back and 2 weeks ahead?

       

      Example:
      YearWeek, YearWeekCounter, Sumoforders
      201251, 208, 5000
      201252, 209, 6000
      201301, 210, 7000
      201302, 211, 8000
      201303, 212, 9000

       

      Best regards: Patric Lindblom

        • Re: Need urgent Help!
          Gysbert Wassenaar

          See attached qvw.

            • Re: Need urgent Help!
              Patric Lindblom

              Hi Gysbert

               

              Thanks for your reply but that's not what i meant, i try to describe it better.
              If the current week for yearweekcounter is 210, then I want the values from the field "orders" summation where yeraweekcounter is 208,209,210,211,212 with dimension Year week(Alla weeks off this will not be displayed).
              Each yearweekcounter ex 208 consists of hundreds of individual lines on a date that fit this week's counter, therefore required sum (orders).

              I had this expression before:

              Money ((if ([Year Week]> = (vCurrentYearWeek-7), sum (Orders) / 1000), '#. # # 0, tkr') with the dimension Yearweek.

              The problem is that this does not work well at year-end when the current week -7 will not be the same as 7 weeks back.

              Therefore, I would like to use the weekcounter instead.("current" weekcounter-7)

               

              Patric

                • Re: Need urgent Help!
                  Gysbert Wassenaar

                  See updated qvw. Is that what you need? I use a trigger to select the current YearWeek. I only have some sample data, but it should work the same with lots of order records.

                    • Re: Need urgent Help!
                      Patric Lindblom

                      Hi Gysbert

                       

                      No sorry, it was not what i meant.

                      But i have now solved it myself through the script with this:

                       

                       

                      tToday_weekcounter:

                      Load distinct

                      [Year Week]

                      Week Counter,

                      if (Week Year (TODAY ()) & num (Week (TODAY ()), 00) = [Year Week] Week Counter) as tTodayweekcounter

                      Resident Time;

                      left join (Time)

                      Today_weekcounter:

                      Load distinct

                      tTodayweekcounter as Today Week Counter

                      Resident tToday_weekcounter

                      where tTodayweekcounter> 0;

                      drop table tToday_weekcounter;

                       

                      Then i can compare the current Yearweek number with the rolling YearWeek number.

                       

                      Thank you for trying to help me.