Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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'
Thanks, but I'm looking more for formula solution.
This statment wouldn't work, because it doesn't check back for past values of a row.
Thanks for your input.
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])),'YES','No')