Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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’
)
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!
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!