topic Re: sum with different results - PivotTbl vs Table in QlikView

sum with different results - PivotTbl vs Table

Anonymous — Mon, 04 Feb 2013 13:39:03 GMT

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

Re: sum with different results - PivotTbl vs Table

alexandros17 — Mon, 04 Feb 2013 13:41:24 GMT

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

Anonymous — Mon, 04 Feb 2013 13:53:12 GMT

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

Re: sum with different results - PivotTbl vs Table

alexandros17 — Mon, 04 Feb 2013 13:58:19 GMT

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

Anonymous — Mon, 04 Feb 2013 14:17:29 GMT

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

hic — Mon, 04 Feb 2013 14:49:41 GMT

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

Anonymous — Tue, 05 Feb 2013 08:47:48 GMT

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

hic — Tue, 05 Feb 2013 14:18:37 GMT

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

Anonymous — Tue, 05 Feb 2013 16:19:59 GMT

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

Re: sum with different results - PivotTbl vs Table

hic — Tue, 05 Feb 2013 16:42:04 GMT

Not sure, but I strongly suspect the Join.

HIC

Re: sum with different results - PivotTbl vs Table

Anonymous — Wed, 06 Feb 2013 07:46:35 GMT

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

Anonymous — Mon, 11 Feb 2013 07:45:43 GMT

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