Beast Mode to compare the record from the recent Monday to the last Monday

ozarkram

Hi All,

I have a dataset that has the the different schedule codes by date at a PO number level..I am trying to identify the records from the most recent monday and compare it to the immediate last monday and then flag the combination of (POnumber /schedule_code) to be old (meaning this combination existed the last monday otherwise I have to flag it as new.. I have to consider only the most recent monday and the immediate last monday for consideration

Input table

Output table

In the above case 07/18 is the most recent monday and 07/11 is the immediate last monday for comparison...Also attached the excel sheet data

Can you please help me here..

Sample data for new or old order system.xlsx

mhouston

I would do this in an ETL by filtering your dataset to this monday, and then using a second branch, filtering to last monday. Then you can right join last monday to this monday on date, po number, and schedule code. You can use a formula tile and set it to "new" when the left side po number is empty, and "old" when the left side po number is populated.

GrantSmith

You can utilize a formula to calculate the most recent Monday for the week:

`Date` - INTERVAL (CASE WHEN DAYOFWEEK(`Date`) = 1 THEN 6 ELSE (DAYOFWEEK(`Date`) - 2) END) DAY

A week prior:

`Date` - INTERVAL (CASE WHEN DAYOFWEEK(`Date`) = 1 THEN 6 ELSE (DAYOFWEEK(`Date`) - 2) END) DAY - INTERVAL 7 DAY

DAYOFWEEK returns a number between 1 (Sunday) and 7 (Saturday).

The CASE statement is checking if it's a Sunday to subtract 6 days instead of 1.

-2 is to reduce the days to subtract to get to Monday. For example Tuesday (3) we need to subtract only 1 day instead of 3 days.

You can then filter your data where the date and the Monday date match. Twice - once for this week, once for last week.

To populate the New vs Old use a Formula tile as @mhouston recommended:

CASE WHEN `Left PO Column` IS NULL THEN 'New' ELSE 'Old' END

ozarkram

Thank you so much @GrantSmith !..Really appreciate your help!

ozarkram

Thank you so much @mhouston !..really appreciate your help!