Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
madhuparnadhar
Contributor III
Contributor III

Set analysis with date

Hi All,

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

Promotion NameStart date
P101-01-2019
P215-01-2019

 

I have an order table with all order details.

DateOrder IDPromotion NameOrder Total
01-01-2019O1P1100
04-01-2019O2P1200
15-01-2019O3P2500
28-01-2019O4P21000
20-01-2019O5P11200

 

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

Labels (1)
1 Reply
thannila
Creator
Creator

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)