Flag new value in a row

[#1]

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.

[Anonymous]

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;

[#3]

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'

[#4]

Thanks, but I'm looking more for formula solution.

[#5]

This statment wouldn't work, because it doesn't check back for past values of a row.

Thanks for your input.

[#6]

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')