Orders amount with dates condition

funquasar — Sat, 16 Nov 2024 18:17:55 GMT

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’

)

Re: Orders amount with dates condition

funquasar — Thu, 30 Jul 2020 06:20:14 GMT

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!

topic Orders amount with dates condition in QlikView

Orders amount with dates condition

Re: Orders amount with dates condition