sum with different results - PivotTbl vs Table

[Anonymous]

Dear all,

i have a really tricky Problem. Somehow the aggregation of my data in a pivot table is not working properly.

I've made a screenshot:

In the table the sum is ca 48 and in the pivot its doubled? (P.S: in the Combi Diagramm its also wrong)

Any idea?

cheers chesterluck

[alexandros17]

The reason is that in the pivot table sum must be aggregated by dimension used in chart.

hope it helps

[Anonymous]

no it does not really help. What do you exactly mean by this??

[alexandros17]

I mean that in the pivot table if you have Dimension  A and B and sum(C) as expression you have to

Aggr(sum(C),A,B)

[Anonymous]

I've just tried  - no luck.

When I'm trieng to put sum() in dymension QV shows "// Error in a dynamic dymension" in the column of this dimension.

[hic]

You most likely have a data model that doubles the value. You might have loaded the records twice, or you have an intermediate table between Schichtdatum and [Dauer Zustand] that has two linking values. Anyway, it is impossible to see without seeing an example.

Using the Aggr function, you could correct it. But it is much better to correct the data model instead.

HIC

[Anonymous]

Hello Henric,

your idea seems logical, but how can I test whether I've loaded sth. twice?

And what makes me a bit worried is that the table (see below) shows the date correctly. The sum of the table is 48 and the sum of the pivot is 96 (doubled):

[hic]

If the lower table is a table box, it will only show unique rows, so there may very well be duplicates in the data.

Create a list box with the field "Dauer Zustand" and show frequency for this field (List box Eigenschaften-Allgemein-Häufigkeiten anzeigen). Then you will get the frequencey for each value to the right.

HIC

[Anonymous]

You are right. When adding the occurance information,QlikView shows that the data is duplicated.

Thank you Henric.

one last question:

     - How do I figure out on which stage the data in DLP is douplicated. I always dropped tables after editing sth.

Maybe it somehow happened here (thats the only place where , from my point of view, it could happen):

Maschinenstatus_JOB_Join:

LOAD iPState,
     lGrpState,
     lMacRef,
     dActQuant,
     dActScrap,
     dActShots,
     iMState,
     lJobRef,
     lMacStaRef,
     tBegin,
     tEnd,
     sText
FROM
..\02_Data\Maschinenstatus.qvd
(qvd);

Left Join

LOAD

     lJobRef,
     Auftragsnummer,
     If(substringcount(Auftragsnummer,'+')>0, subfield(Auftragsnummer,'+',1), Auftragsnummer) as Auftragsnummer_NEU,
     lTolRefArt,
     bZusatz,
     dSetQuant,
     dSetShots,
     dSetSpeed,
     iActCavity,
     iSetCavity,
     sType,
     tActBegin,
     tActEnd,
     tReleaseDate,
     Artikelbeschreibung,
     Artikel,
     Werkzeuglagerort,
     Werkzeugbeschreibung,
     Werkzeug,
     Maschinenplatz,
     Maschinenbeschreibung,
     Maschine,
     Ausschussrate
FROM
..\02_Data\JOB_incl_ART_MASCHINE_WRZ_AUSSCHUSS.qvd
(qvd);

Zyklusabweichung:

Load
     lJobRef,
     iPState,
     lGrpState,
     lMacRef,
     dActQuant,
     dActScrap,
     dActShots,
     iMState,
     lMacStaRef,
     tBegin,
     tEnd,
     sText,
     Auftragsnummer,
     Auftragsnummer_NEU,
     lTolRefArt,
     bZusatz,
     dSetQuant,
     dSetShots,
     dSetSpeed,
     iActCavity,
     iSetCavity,
     sType,
     tActBegin,
     tActEnd,
     tReleaseDate,
     Artikelbeschreibung,
     Artikel,
     Werkzeuglagerort,
     Werkzeugbeschreibung,
     Werkzeug,
     Maschinenplatz,
     Maschinenbeschreibung,
     Maschine,
     Ausschussrate,
     If (dSetSpeed > 0 And iMState = 5 And dActShots > 0, (((tEnd - tBegin)/dActShots)-dSetSpeed)/dSetSpeed,1) as [Zyklus Abweichung]

Resident Maschinenstatus_JOB_Join;

Store Zyklusabweichung into ..\02_Data\Maschinenstatus.qvd;

let numTables = NoOfTables();
for i=1 to $(numTables)
let tt = TableNAme(0);
drop table [$(tt)];
next

[hic · Accepted solution]

Not sure, but I strongly suspect the Join.

HIC