Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Register for Events and Webinars directly from Qlik Community: http://bit.ly/2Vpnenx
Highlighted
chesterluck
Contributor II

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.

I've made a screenshot:

PivotTBL vs Table.jpg

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

1 Solution

Accepted Solutions
Highlighted

Re: sum with different results - PivotTbl vs Table

Not sure, but I strongly suspect the Join.

HIC

View solution in original post

11 Replies
Highlighted

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

Highlighted
chesterluck
Contributor II

Re: sum with different results - PivotTbl vs Table

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

Highlighted

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)

Highlighted
chesterluck
Contributor II

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.

Highlighted

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

Highlighted
chesterluck
Contributor II

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

PivotTBL vs Table.jpg

Highlighted

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

Highlighted
chesterluck
Contributor II

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:

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

Highlighted

Re: sum with different results - PivotTbl vs Table

Not sure, but I strongly suspect the Join.

HIC

View solution in original post