Set analysis with date

[madhuparnadhar]

Hi All,

I have a dimension table with promotion name with their corresponding starting dates. 

Promotion Name	Start date
P1	01-01-2019
P2	15-01-2019

I have an order table with all order details.

Date	Order ID	Promotion Name	Order Total
01-01-2019	O1	P1	100
04-01-2019	O2	P1	200
15-01-2019	O3	P2	500
28-01-2019	O4	P2	1000
20-01-2019	O5	P1	1200

Now I want to find out order total during the first week of every promotion. So for promotion P1, I should have order total for O1 and O2, but not O5 which is placed on 20-01-2019 (after first week of promotion P1, i.e. after 07-01-2019).

Similarly for promotion P2, I should have order total for only O3.

Can anyone help me in achieving this ?

Thanks and Regards,

Madhuparna Dhar

[thannila]

T1:
load * Inline
[
Date,Order_ID,Promotion_Name,Order_Total
01-01-2019,O1,P1,100
04-01-2019,O2,P1,200
15-01-2019,O3,P2,500
28-01-2019,O4,P2,1000
20-01-2019,O5,P1,1200
];

T2:
LOAD * INLINE
[
Promotion_Name,Start_date
P1,01-01-2019
P2,15-01-2019
];

T3:
NoConcatenate
load Order_ID,Promotion_Name,Order_Total,
Date(Date#(Date,'DD-MM-YYYY'),'DD/MM/YYYY') AS Date,
Week(Date(Date#(Date,'DD-MM-YYYY'),'DD/MM/YYYY')) AS Week2
resident T1;
DROP TABLE T1;

left join(T3)
T4:
load Promotion_Name,Date(Date#(Start_date,'DD-MM-YYYY'),'DD/MM/YYYY') AS Start_date,
Week(Date(Date#(Start_date,'DD-MM-YYYY'),'DD/MM/YYYY')) AS Week1
RESIDENT T2;
DROP TABLE T2;

Straight table:

=if(Week1=Week2,Date)

=if(Week1=Week2,Order_ID)

=if(Week1=Week2,Promotion_Name)

=if(Week1=Week2,Order_Total)