5 Replies Latest reply: Apr 5, 2013 3:03 PM by rafa_rafa RSS

    Flag new value in a row

      I have 52 week data, and I'd like to flag if there was a new sales in the most recent week - in the example below Week 4 = W4

      In case there was sales for a given product previous to the most recent week, I don't want to flag it.

      Please see example below

       

                          W1     W2     W3     W4        FLAG

      Product A                                     2           Yes

      Product B       1                   2                     No

      Product C                            1                     No

       

      List of products is quite large about 70K rows.

        • Re: Flag new value in a row
          Aaron Couron

          This might be more complicated than necessary, but here is the solution I came up with:

           

          Data:
          LOAD * INLINE [
          Week, Product, Sales
          1, B, 100
          3, B, 120
          3, C, 90
          4, A, 150]
          ;

          MaxWeek:
          LOAD MAX(Week) AS MaxWeek
          RESIDENT Data;

          LET vMaxWeek = PEEK('MaxWeek',0,'MaxWeek');

          JOIN (Data)
          SoldItems:
          LOAD Product AS OldSale,
          Product
          RESIDENT Data
          WHERE Week<>'$(vMaxWeek)';

          JOIN (Data)
          LOAD Product,
          1
          AS NewSaleFlag
          RESIDENT Data
          WHERE NOT EXISTS(OldSale,Product);

          DROP FIELD OldSale;

          • Re: Flag new value in a row

            in a simple pivot table you could have the FLAG column as an expression which could written with a macro such as

             

            =If(Week=4,'Yes','No')

             

            As Week would be based upon a Date you could have the conditional statement based upon a the a function to return the last week rather than a value of '4'

            • Re: Flag new value in a row

              Below see my solution. You can be more elaborate and set max/min for dates to pick just the most recent week, but this works perfrectly.

              Once you set your flag "YES", "No", you can come up with another column for your last week sales

               

               

              if

              (sum({$<[Products] = {'MYRBETRIQ'},[Weeks]={'>=10/26/2012<=3/8/2013'}>} [Sales])
              <
              sum({$<[Products] = {'MYRBETRIQ'},[Weeks]={'3/15/2013'}>} [Sales]

              ),'YES','No')