Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Set analysis with date

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,

Labels (1)
• ### Set Analysis

Creator

T1:
[
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:
[
Promotion_Name,Start_date
P1,01-01-2019
P2,15-01-2019
];

T3:
NoConcatenate
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:
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)

Tags