The problem I am facing is that when you select a Key_promotion I want to see the sales amount of the promotion while the promotion was active. What I also want is the sales amount before the promotion was active.
So if I select Promotion 1 I would like to get the table on my dashboard as:
since the promotion does not change for each sale, you can join it in the script. i added a promoID and a salesID to allow for testing and the promoID to indicate which promotion applies:
promotions:
load *, rowno() as PromoID inline [
Key_promotion,Start_promotion,End_promotion,EAN_code
Promotion 1,2/11/2020,8/11/2020,A
Promotion 1,2/11/2020,8/11/2020,B
Promotion 1,2/11/2020,8/11/2020,C
Promotion 1,2/11/2020,8/11/2020,D
Promotion 2,9/11/2020,15/11/2020,E
Promotion 2,9/11/2020,15/11/2020,F
];
Sales:
load *, RowNo() as SalesID inline [
EAN_code1,Key_promotion1,Date,Amount
A,,26/10/2020,2
A,,27/10/2020,4
B,,28/10/2020,6
B,,29/10/2020,3
C,,30/10/2020,1
D,,30/10/2020,4
A,Promotion 1,2/11/2020,1
A,Promotion 1,3/11/2020,2
B,Promotion 1,3/11/2020,4
B,Promotion 1,5/11/2020,7
C,Promotion 1,6/11/2020,2
D,Promotion 1,7/11/2020,12
];
left join (Sales)
load PromoID, EAN_code as EAN_code1,Key_promotion as Key_promotion1, Start_promotion as Start_promotion1,End_promotion as End_promotion1 resident promotions;
NoConcatenate
NewSales:
load SalesID, PromoID, Date, Amount Resident Sales
where Date>=Start_promotion1 and Date <=End_promotion1 or
isnull(PromoID) and not isnull(SalesID);
drop table Sales;
