combing and Using different dates in the same report

[Mahathi]

Good Morning, I have a Qlik sense requirement - building 2 table reports using the following columns from raw data as below. order date, order_id, channel, returned _date, qty_ordered, qty_returned.  All from same sales table.

I also have a date column from master calendar.

order date	order_id	channel	returned date	qty_ordered	qty_returned
8/3/2020	28948	direct	8/28/2020	14	12
8/3/2020	92934	paid	8/27/2020	21	20
8/4/2020	92936	display	9/11/2020	22	22
8/27/2020	99526	display	10/1/2020	13	13
9/5/2020	95007	social	10/15/2020	21	21
9/5/2020	95070	social	10/15/2020	21	21
9/26/2020	67176	paid	10/28/2020	19	19
10/7/2020	75627	direct	11/3/2020	26	26
10/7/2020	75843	paid	11/20/2020	11	11
10/29/2020	99143	display	11/24/2020	31	30
11/3/2020	91641	shopping	11/25/2020	23	22
11/4/2020	99252	paid	11/30/2020	27	27
11/5/2020	94192	social	11/30/2020	20	20
11/30/2020	27333	direct	1/12/2021	17	17
11/30/2020	32145	paid	1/14/2021	13	13

Report 1:

I need to build a report with sum(qty_ordered) and sum(qty_returned) as measures and date as dimension. Sum(qty_ordered) should be rolled up to order date, however I need to show the Sum(qty_returned) rolled up at the returned date instead of the order date as shown in the example below.

Since the order date in sales table is associated with the date in the master calendar, I don’t have an issue with qty_ordered but to show the qty_returned at the returned date I need suggestions.  

In the example below for order date 8/27/2020 Sum(qty_ordered) is 13 but for returned date 8/27/2020 qty_returned is 21. These 2 quantities need to be displayed in the same line with the same date even though they do not belong to the same transaction.

date	Sum(qty_ordered)	Sum(qty_returned)
8/3/2020	35	0
8/4/2020	22	0
8/27/2020	13	20
8/28/2020	0	12
9/5/2020	42	0
9/11/2020	0	22
9/26/2020	19	0
10/7/2020	37	0
10/15/2020	0	42
10/28/2020	0	19

Report 2:

In the same way for second report as well, the qty_returned should be shown at the returned date instead of order date. The qty_ordered should still be at the order date. But here I am using another dimension channel.

Here as well for order date 8/27/2020 Sum(qty_ordered) is 13 for channel ‘display’ but for returned date 8/27/2020 qty_returned is 21 for channel ‘paid’, but both need to be displayed under the same date 8/27/2020 even though they are not same channel, hence why we have 2 lines one for each channel. I want to be able to implement this logic for multiple other dimensions not just channel in different reports.

date	channel	Sum(qty_ordered)	Sum(qty_returned)
8/3/2020	direct	14	0
8/3/2020	paid	21	0
8/4/2020	display	22	0
8/27/2020	display	13	0
8/27/2020	paid	0	20
8/28/2020	direct	0	12

[Vegar]

Consider remodelling your master calendar handling so that all fields are associated to the master calendar. It could make your reports easier to handle.

Take look at the Tutorial using common date dimensions by Rob Wunderlich and the Canonical Date by HIC