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

# 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

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)
1
AS NewSaleFlag
RESIDENT Data
WHERE NOT EXISTS(OldSale,Product);

DROP FIELD OldSale;

• ###### Re: Flag new value in a row

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

• ###### 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

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

Thanks for your input.

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