Orders amount with dates condition

[funquasar]

Hello, Comminity!

Task was look simple for me, but I lost three days trying to solve this. In SQL (example below) I write query in less than minute 🙂

How get the amount of orders, where OrderDate and SalesDate of which are included in the applied date filter?

There are two tables: orders and sales, which I concatenate into one and a calendar. The calendar connects to data via a DateBridge.

Orders:
LOAD * INLINE [
   RowNo, OrderDate, OrderNo
    1, 06/28/2020, 1
    2, 06/29/2020, 2
    3, 06/30/2020, 3
    4, 07/01/2020, 4
    5, 07/02/2020, 5
    6, 07/03/2020, 6
    7, 07/04/2020, 7
    8, 07/05/2020, 8
    9, 07/06/2020, 9
    10, 07/07/2020, 10
];

Concatenate
Sales:
LOAD * INLINE [
    RowNo, OrderNo, ShippedDate, Quantity
    11, 1, 06/28/2020, 10
    12, 2, 06/29/2020, 20
    13, 3, 07/04/2020, 30
    14, 4, 07/03/2020, 10
    15, 5, 07/03/2020, 20
    16, 6, 07/04/2020, 30
    17, 7, 07/10/2020, 10
    18, 8, 07/11/2020, 20
    19, 9, 07/12/2020, 30
    20, 10, 07/10/2020, 10
];

DateBridge:
LOAD 
     RowNo,
     OrderDate as CanonicalDate,
     'Order'   as DateType
Resident
     Orders;
    
Concatenate
LOAD 
     RowNo,
     SalesDate as CanonicalDate,
     'Sales'   as DateType
Resident
     Orders;
 

The calendar is made by a typical script, so I will save space 🙂

Let's say in CanonicalDate I choose the period from 06/30/2020 to 07/04/2020. The result includes orders 3,4,5,6, amount = 90. The 7th does not include because the SalesDate > CanonicalDate in filter, although OrderDate meets the filter condition.

In SQL, I get similar data with the following query:

SELECT

            SUM(Quantity)

FROM

            Table

WHERE

            OrderNumber in (

                                                 SELECT

                                                              OrderNumber

                                                 FROM

                                                              Table

                                                 WHERE

                                                              OrderDate BETWEEN ‘06/30/2020’ AND ‘07/04/2020’

                                                              and

                                                              SalesDate BETWEEN ‘06/30/2020’ AND ‘07/04/2020’

                                     )

[funquasar · Accepted solution]

So, I think I found a solution - I added the OrderDate column to the Sales table. Create two variables:

vStartDate (Min(CanonicalDate))

and

vEndDate (Max(CanonicalDate)), and created a expression on the histogram:

sum({$<

          [OrderDate] = {"$(='>='&vStartDate&'<='&vEndDate)"},

          [SalesDate] = {"$(='>='&vStartDate&'<='&vEndDate)"}

         >}

         Quantity)

But, on my histogram as Measurement i use cyclic group (Year, Month, Week, DayMonth), and Quantity is wrong. 

I think because the expression is calculated based on the values of the variables, not the calendar, so QV cannot decompose the data automatically on the graph when fields in date group are switched. I don’t know how to describe the current situation more clearly, and I think this is a separate thread, so thank you all.
Good luck!