11 Replies Latest reply: Feb 11, 2013 2:45 AM by Michael Ionkin

# sum with different results - PivotTbl vs Table

Dear all,

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

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

• ###### Re: sum with different results - PivotTbl vs Table

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

hope it helps

• ###### Re: sum with different results - PivotTbl vs Table

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

• ###### Re: sum with different results - PivotTbl vs Table

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)

• ###### Re: sum with different results - PivotTbl vs Table

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.

• ###### Re: sum with different results - PivotTbl vs Table

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

• ###### Re: sum with different results - PivotTbl vs Table

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):

• ###### Re: sum with different results - PivotTbl vs Table

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

• ###### Re: sum with different results - PivotTbl vs Table

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:

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

Left Join

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:

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

• ###### Re: sum with different results - PivotTbl vs Table

Not sure, but I strongly suspect the Join.

HIC

• ###### Re: sum with different results - PivotTbl vs Table

well, I don't think that it is the join (anyway I will test it). I think it is connected to the 3rd load (resident load).

See, im doing first a left join and then, in order to perform an if statement with data from both tables, I'm doing the resident load. I don't know whether it is the correct way...

P.S.: i found here sth., maybe it leads to the solution:

http://community.qlik.com/message/159983#159983

• ###### Re: sum with different results - PivotTbl vs Table

Issue solved. It was a join on a later stage. Thx for the support