Sum of deliveries, contracts and so on in one chart - canonical date

[Frontierchen]

Hello everyone,

Hopefully this is the right board for our question. I am quite new to QlikSense 🙂

We are trying to achieve to build a chart with several expressions. One is the amount of open and closed purchase orders and one is the amount of sales orders. Purchase orders (Bestelllieferdatum) and sales orders (SalesOrderlieferdatum) have their own shipping dates.

We get our data from the ERP system SAP Business One via an ODBC connection. We use Qlik Data Transfer to upload these tables as qvd to Qlik Sense Cloud.

Below is an image of our data model:

The script looks like this:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY [.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='de-DE';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan.;Feb.;März;Apr.;Mai;Juni;Juli;Aug.;Sep.;Okt.;Nov.;Dez.';
SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';
SET DayNames='Mo.;Di.;Mi.;Do.;Fr.;Sa.;So.';
SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Sales_Row_Data:
LOAD
DocEntry,
ItemCode,
Dscription,
Quantity as Auftragsmenge,
ShipDate as SalesOrderlieferdatum,
OpenQty as Kundenauftragsmenge,
LineTotal,
OpenSum,
OpenSumFC,
PriceBefDi,
DocDate as Auftragsbuchungsdatum,
OpenCreQty as Offeneauftragsmenge,
OpenSumSys,
QtyToShip,
InvQty,
OpenInvQty,
InvQtyOnly,
U_QYCPrchConfN,
U_QYCPrchConfD,
U_QYCOrigShipD
FROM [lib://KTK Datenbereich:DataFiles/RDR1.qvd]
(qvd);

Purchase_Order_Rows_Data:
LOAD
DocEntry,
Quantity as Bestellmenge,
ShipDate as Bestelllieferdatum,
Price,
LineTotal as BestellLineTotal,
OpenCreQty as Offenebestellmenge,
U_QYCOrigShipD as UrBestelllieferdatum
FROM [lib://KTK Datenbereich:DataFiles/POR1.qvd]
(qvd);

Delivery_Rows_Data:
LOAD
DocEntry,
Dscription as Auslieferbezeichnung,
Quantity as Ausliefermenge,
ShipDate as Lieferungsdatum,
SubCatNum,
BaseDocNum,
INMPrice,
StockSumSc,
StockValue,
isSrvCall
FROM [lib://KTK Datenbereich:DataFiles/DLN1.qvd]
(qvd);

Purchase_Order_Data:
LOAD
DocEntry,
DocNum as Bestellnummer,
CANCELED as BestellCanceled,
DocStatus as Bestellstatus,
CardName,
TaxDate as Bestellbelegdatum
FROM [lib://KTK Datenbereich:DataFiles/OPOR.qvd]
(qvd);

Sales_Order_Data:
LOAD
DocEntry,
DocNum as Auftragsnummer,
CANCELED as SalesCanceled,
DocStatus as Auftragsstatus,
DocDueDate as Auftragslieferdatum,
CardCode,
TaxDate as Auftragsbelegdatum,
CancelDate
FROM [lib://KTK Datenbereich:DataFiles/ORDR.qvd]
(qvd);

Items_Table_Data:
LOAD
FrgnName,
ItemCode,
ItemName,
ItmsGrpCod,
OnHand,
OnOrder,
BuyUnitMsr,
LastPurPrc,
MinOrdrQty
FROM [lib://KTK Datenbereich:DataFiles/OITM.qvd]
(qvd);

Delivery_Table_Data:
LOAD
DocEntry,
DocNum as Lieferscheinnummer,
DocType,
CANCELED as LieferscheinCanceled,
DocStatus as Lieferscheinstatus,
DocDate as Lieferscheindatum,
DocDueDate as Lieferdatum,
Comments,
Confirmed,
Address,
Address2,
FatherCard,
FatherType,
NumAtCard,
ShipToCode,
TaxDate as Lieferscheinbelegdatum,
U_Auftrag
FROM [lib://KTK Datenbereich:DataFiles/ODLN.qvd]
(qvd);

SalesOrderCalendar:
LOAD
ShipDate as SalesOrderlieferdatum,
Month(ShipDate) as SalesOrderLiefermonat,
Day(ShipDate) as SalesOrderLiefertag,
Year(ShipDate) as SalesorderLieferjahr
FROM [lib://KTK Datenbereich:DataFiles/RDR1.qvd]
(qvd);

BestelllieferCalendar:
Load
ShipDate as Bestelllieferdatum,
Month(ShipDate) as Bestellliefermonat,
Day(ShipDate) as Bestellliefertag,
Year(ShipDate) as Bestelllieferjahr
FROM [lib://KTK Datenbereich:DataFiles/POR1.qvd]
(qvd);

LieferungsCalendar:
Load
DocDueDate as Lieferungsdatum,
Month(DocDueDate) as Lieferungsdatummonat,
Day(DocDueDate) as Lieferdatumtag,
Year(DocDueDate) as Lieferdatumjahr
FROM [lib://KTK Datenbereich:DataFiles/ODLN.qvd]
(qvd);

DateBridge:
LOAD
'SalesOrderlieferdatum' as Flag_Date
,DocEntry
,SalesOrderlieferdatum as Canonical_Date
Resident Sales_Row_Data;

Concatenate

LOAD
'Bestelllieferdatum' as Flag_Date
,DocEntry
,Bestelllieferdatum as Canonical_Date
Resident Purchase_Order_Rows_Data;

Concatenate

LOAD
'Lieferungsdatum' as Flag_Date
,DocEntry
,Lieferungsdatum as Canonical_Date
Resident Delivery_Rows_Data;

MasterCalendar:
Load
Date(TempDate) as Canonical_Date
,Year(TempDate) as CanYear
,'Q' & Ceil(Month(TempDate)/3) as CanQuarter
,Month(TempDate) as CanMonth
,Week(TempDate) as CanWeek
,Date(MonthStart(TempDate),'MMM-YYYY') as CanMonthYear
,WeekDay(TempDate) as CanWeekDay
;
Load
MinDate + IterNo() - 1 as TempDate
While MinDate + IterNo() - 1 <=MaxDate;
Load
Min(FieldValue('Canonical_Date', RecNo()))-1 as MinDate
,Max(FieldValue('Canonical_Date', RecNo())) as MaxDate
AutoGenerate FieldValueCount('Canonical_Date');

Somewhere we are struggling with the canonical date. As you can see in the sheet, if we choose the year 2023 - the range of the canonical date is correct from 1 January to 31 December. However, it does not select the correct range for the order delivery date (Bestelllieferdatum - top right list box) and the sales order delivery date (SalesOrderlieferdatum - bottom right list box).

So maybe one of you has an idea of what we are struggling with. Unfortunately I am unable to upload the full Qlik Sense app due to company data.