Durchschnitt berechnen

[#1]

Hallo liebe Qlik-Community,

ich hätte da mal ein paar Fragen an euch.

Aber erst einmal das Beispiel.
 


Ich habe eine Anzahl an Kisten (QuantityAbsolute) für jede einzelne Stunde(HourCreatedOn) eines Tages (DateCreatedOn).

Daraus lässt sich jetzt auch ganz einfach die Gesamtanzahl an Kisten pro Tag ausrechechnen und auch darstellen.

Bei dem Durchschnitt an Kisten für jede Stunde von allen Tagen zusammen, also Tag 1 Stunde 5 + Tag 2 Stunde 5 + Tag 3 Stunde 5 usw...), wird es schon schwierig.

Mit der Formel sum(QuantityAbsolute)kommt man auf die 47904( Ergebnis von: "Gesamt Zeile" und "Gesamt Spalte").

Also müsste ja demzufolge sum(QuantityAbsolute)/7 die 6843.43 (Summe der Durchschnitte der einzelnen Stunden) ergeben.

Mit dieser Formel komme ich, wenn ich es in ein Diagramm einfüge, auch auf mein gewünschtes Ergebnis. Das Problem ist jedoch, dass ich nicht immer genau 7 Tage habe. Somit ist diese Formel wieder hinfällig.

Nach einigen Recherchen kam ich auf eine weitere Formel: Avg(Aggr(sum(QuantityAbsolute),DateCreatedOn))

Diese gibt bei einer Darstellung via KPI auch die gewünschten 6843.43 wieder. Aber in einem Liniendiagramm bekomme ich nur unauswertbare Daten heraus, die ziemlich weit von dem eigentlichen Ergebnis entfernt liegen.

Könntet ihr mir hier vielleicht weiterhelfen eine verlässliche Formel zu finden, die mir den Durchschnitt der Kisten für jede einzelne Stunde wiedergibt?

Zudem brauche ich auch noch eine Darstellungsmöglichkeit für die Durchschnittswerte der einzelnen Tage.

Hier komme ich bis jetzt leider nur über die Verwendung der Formel: Avg(Aggr(sum(QuantityAbsolute),DateCreatedOn))heran.

Rechnet man jetzt Avg(Aggr(sum(QuantityAbsolute),DateCreatedOn))/24 kommt man auf die Summe der Durchschnitte der einzelnen Tage: 1996. Im Prinzip könnte man diese Formel so lassen oder? Ein Tag kann ja nicht mehr als 24 Stunden haben.

Oder gibt es hier noch eine andere Formel, die effektiver (bzw. einfacher) zu verstehen ist.?

Als letzten Punkt würde ich gern den Wert 285.14 darstellen. Dieser Wert ist der Durchschnittswert der Durchschnitte der einzelnen Tage bzw. der einzelnen Stunden. Gibt es da auch eine Formel für, die auf die Veränderung der Anzahl der Tage reagieren kann?

Für eure Hilfe danke
ich euch vielmals!!!

Viele Grüße

André

[marcus_sommer]

Hallo André,

sieht insgesamt schon ganz gut aus. Den Wert von 6843.43 kann man aber nicht so ohne weiteres in einem Linien-Diagramm abbilden, da es eine Total-Berechnung von auf Tagesbasis summierte Stundendurchschnitte ist - man kann den Wert also nicht gegen Stunden und Tag als Dimension darstellen, eine übergeordnete Dimension wie Monat sollte aber funktionieren.

Der Wert 1996 lässt wohl einfacher per sum(QuantityAbsolute) / count(distinct Stunde) abbilden und für 285.14 müsste die aggr-funktion vermutlich nur um die Stunde erweitert werden, in etwa wie: Avg(Aggr(sum(QuantityAbsolute),DateCreatedOn, Stunde).

- Marcus

[#3]

Hallo Marcus,

Ich möchte mich schon mal vielmals für deine Antworten bedanken.

Gibt es wirklich keine weitere Möglichkeit den Durchschnitt der Stunden 0 bis 23 von mehreren Tagen zusammen darzustellen?

Die Formel  sum(QuantityAbsolute)/count(distinct HourCreatedOn)  gibt leider nicht ganz die Werte aus, die ich zuvor schon mal in der ExcelTabelle ausgerechnet hatte .

Hier die Unterschiede anhand des obigen Beispieles:

In einer KPI Darstellung kommt man mit dieser Formel auf den richtigen Wert. Wenn man die gleiche Formel in ein Liniendiagramm einsetzt, gibt er mir die oben genannten Werte heraus. Komisch ist, dass die Werte vom 03.11 und vom 05.11 übereinstimmen. Muss ich beim einsetzen der Formel in ein Liniendiagramm o.ä. noch einen weiteren Zusatz in der Formel verwenden?

Bei der Formel Avg(Aggr(sum(QuantityAbsolute),DateCreatedOn,HourCreatedOn)) komm ich weder mit einer KPI-Anzeige, noch mit einem anderen Diagramm auf die 285.14

Gibt es hier noch eine andere Möglichkeit auf das Ergebnis zu kommen?

Vielen Dank für deine Hilfe!

Andre

[marcus_sommer]

Hallo Andre,

ich denke schon, dass das geht, jedoch ist es nur mit diesen Beschreibungen hier recht schwierig zu erkennen, was falsch sein könnte. Das einfachste ist, Du stellts hier eine kleine Beispiel-Anwendung mit ein paar Fake-Daten ein, bei der aber die Datenstruktur und was wo wie abgebildet werden soll enthalten ist (wenn das Qlik Sense ist und nicht QlikView kann ich dann aber nicht weiterhelfen).

- Marcus

[swuehl]

Nur eine kleine Bemerkung:

3.11. und 5.11. sind die beiden Tagen, an denen zu jeder Stunde QuantityAbsolute >0 ist.

Vielleicht hast du ein Problem mit Nullwerten bzw. NULL? Wie sehen denn deine Quelldaten aus?

Fehlen da vielleicht Records zu bestimmten Uhrzeiten / Datumswerten?

edit:

Ja, mal ein paar Durchschnittswerte mit der Distinkten Anzahl der Stunden >0 durchgerechnet, meine Annahme scheint zu stimmen.

Also vielleicht einfach durch 24 teilen? oder

sum(QuantityAbsolute)/count(TOTAL distinct HourCreatedOn)

[#6]

Hallo Swuehl,

als Quelldaten habe ich :

Auftragsnummer ( SO_ASN)

Anzahl der Kisten ( QuantityAbsolute)

Kalenderwoche (WeekCreatedOn)

Datum ( DateCreatedOn)

Stunde (HourCreatedOn)

Minute ( MinuteCreatedOn)

Art der Kiste ( ItemNameShort)  - Kann aber hier vernachlässigt werden, da ich nur die Daten zu einem Kistentyp

                                                     habe.

Wie meinst du das mit den Nullwerten?

Ich habe leider überall wo oben eine Null steht keine Datensätze, da zu diesen Zeiten nicht gearbeitet wurde.

Ich habe aber gerade noch ein wenig darüber nachgedacht:

Ist es möglich in Qlik Sense eine Formel zu entwickeln, die anhand meiner Datumsauswahl ( habe ich erstmal über einen "DateCreatedOn"- Filter gemacht) eine absolute Anzahl an Tagen errechnet?

Hier mal mein Denkansatz:

Sum({<HourCreatedOn>}[QuantityAbsolute])/7

Diese Formel stellt genau das dar, was ich gerne sehen möchte.

Das macht Sie aber nur, wenn ich über den DateCreatedOn - Filter die Tage 02.11.2015 bis einschließlich 08.11.2015 (7 Tage) auswähle.

Jetzt habe ich noch etwas anderes Gefunden, dass man an stelle der 7 eintragen kann:

sum({<HourCreatedOn>}[QuantityAbsolute]) / (Date#('08-11-2015', 'DD-MM-YYYY') -  Date#('01-11-2015', 'DD-MM-YYYY'))

(Den 1.11.2015 hab ich gewählt, weil ich sonst nicht auf die 7 Tage gekommen wäre)

Diese Formel bildet ja einfach nur die Differenz aus den gewählten Tagen.

Also:

Gibt es eine Formel, die abhängig von meiner Auswahl an Tagen eine Absolute Anzahl an Tagen berechnet?

Vielen dank für deine Hilfe

Andre

[swuehl · Accepted solution]

Also sowas wie

=Sum( [Quantity Absolute]) / Count(Distinct [DateCreatedOn])

?

[#8]

Genau das habe ich gesucht

Nur mit dem Zusatz TOTAL

=Sum( [Quantity Absolute]) / Count(TOTAL Distinct [DateCreatedOn])

Vielen Dank!!!

André