Calculating date values

Sat, 21 Nov 2009 14:48:43 GMT

Hello,

avoid some existing synthetic key, I joined 3 tables (questions, quotations and orders, from an Excel list) with a CONCATENATE LOAD command.

Stammdaten:
LOAD AnfrageID,
 AnfrageEingang AS Anfrageeingang,
 AnfrageEingang AS Datum,
 AnfrageBearbeitung AS Anfragebearbeitung,
 AnfrageBearbeitung - AnfrageEingang AS AnfrageBearbeitungszeit,
 KundenNr AS Kundennummer,
 PersonalNr AS Personalnummer,
 Besuch AS BesuchJaNein,
 Angebot AS AngebotJaNein
FROM
[DataSources\VirtuelleFirma.xlsx]
(ooxml, embedded labels, table is Anfragen);
CONCATENATE LOAD AngebotID,
 AnfrageID,
 AngebotErstellung AS Angeboterstellung,
 AngebotAbgabe AS Angebotabgabe,
 AngebotAbgabe AS Datum,
 AngebotAbgabe - AngebotErstellung AS AngebotBearbeitungszeit,
 KundenNr AS Kundennummer,
 PersonalNr AS Personalnummer
FROM
[DataSources\VirtuelleFirma.xlsx]
(ooxml, embedded labels, table is Angebote);
CONCATENATE LOAD AuftragNr AS Auftragsnummer,
 AngebotID,
 KundenNr AS Kundennummer,
 PersonalNr AS Personalnummer,
 Bestelldatum,
 Bestelldatum AS Datum,
 Lieferdatum,
 Lieferdatum - Bestelldatum AS Lieferzeit,
 RechnungNr AS Rechnungsnummer,
 Rechnungdatum,
 ZahlungBis AS Zahlungsziel
FROM
[DataSources\VirtuelleFirma.xlsx]
(ooxml, embedded labels, table is Auftrag);

Now I want to calculate how much time has elapsed, for example the receipt of a request until the establishment of the tender or the preparation of the bid until the issuance of the contract. This is what I want to spend both in a pie chart as well as in a table (table diagram). By combining the tables are indeed but to compare date values not in a record. My approach now was summed up in aggr () according to the common dimensions of the totals. How could I do that?

To better illustrate, I have just created a test document and a screenshot of my table structure, which express my problem in the graphs below right and bottom middle.

-> Test.qvw
-> testab.jpg

Thank you very much
Thomas

Calculating date values

prieper — Sun, 22 Nov 2009 02:06:48 GMT

Think it depends a bit from your input-data, if there is a strict rule, that for each Order (Auftragsnummer) there is only one Offer (AngebotID), and for each Offer there is only one Query (AnfrageID), would use a JOIN and have for each Query just one line, which makes then your calculations much easier.
If Kundennummer, Personalnummer are different, you may record them as Kundennummer_Anfrage, Kundennummber_Angebot etc.

HTH
Peter

topic Calculating date values in QlikView

Calculating date values

Calculating date values