Hi :),
I need to count sales in 7, 14, 21 days and after a month. I've prepared data which I include below:
first table is with id and sentDate:
table1:
load * Inline
[
id, sentDate
5902, 2021-07-16
5900, 2021-07-14
5877, 2021-07-01
5876, 2021-06-30
5421, 2021-07-13
]
;
and second table is with Invoice_num, date and email:
table2:
load * Inline
[
Invoice_num, date, email
Q/123, 2021-07-16, abc@gmail.com
W/123, 2021-07-17, abcedes@gmail.com
W/345, 2021-07-19, sff@gmail.com
W/952, 2021-07-19, dedff@gmail.com
W/9d52, 2021-07-23, dedff@gmail.com
W/987, 2021-07-25, bvc@gmail.com
S/145, 2021-07-05, ems@op.pl
T/486, 2021-07-13, yeft@gmail.com
E/415, 2021-01-01, yeft@gmail.com
E/4185, 2021-07-30, yeft@gmail.com
]
;
I would like also to connect these tables with that one below(optional):
tableEmail:
load * Inline
[
id, email
5902, abc@gmail.com
5902, abcedes@gmail.com
5902, sff@gmail.com
5900, abc@gmail.com
5877, bvc@gmail.com
5876, bpty@wp.pl
5877, ems@op.pl
5900, yeft@gmail.com
5900, sff@gmail.com
];
Final question is how to use interval match or master calendar to connect tables and display something like that:
| id | sentDate | after 7 days | count |
| 5421 | 13.07.2021 | 20.07.2021 | 5 |
| 5876 | 30.06.2021 | 07.07.2021 | 1 |
| 5877 | 01.07.2021 | 08.07.2021 | 1 |
| 5900 | 14.07.2021 | 21.07.2021 | 4 |
| 5902 | 16.07.2021 | 23.07.2021 | 5 |
